Savings with interest and regular payments

[bonjella]

Hello,

I have searched the internet for help with this, but anything relivent
seems geared towards paying off loans rather than saving so my answers
are coming out negitive, or just wrong.

Hopefully there is an easier way of doing this.

I am trying to save up a deposite for my first home... and I want to
know how long it'll take me.

I have a starting balance, the annual interest rate of my savings
account, and how much I can save each month. I also have a target
ammount in mind.

From this I want to know how many Months I will have to save this

before my Target is met.

On a seporate issue I want to know, if I want to buy a home in 2 years
time, how much I'll have saved by then, but I think this is possible
using NPER, again it's currently coming out 'backwards' and it's still
needs a target ammount.

I hope you can point me in the right direction

many thanks,

Amy

 

[JLatham]

I believe you can use NPER for this.
Lets say your starting balance is zero, the ANNUAL rate paid is 3% (of
course if you were borrowing it, it would be at least triple that), and you
decide you can save $100/month and want $2000 as your goal.
=ROUNDUP(NPER(.03/12,-100,0,2000),0)
should give you the number of months in even numbers (not 19.54 months, but
20 instead).
The trick is to enter that monthly deposit to savings as a negative number.

You can set your own amortization/savings estimator up easily enough:
A1 = Annual percentage Rate as a percent, i.e. 3%
A2 = 12 (both number of months/year)
A3 = anticipated monthly deposit (100)
A4 = starting balance (0)
A5 = Savings Goal (2000)

Now after entering those values, drop down to about row 8 (why? because
that's what I did <g>). Type headers for columns A B C and D on row 8:
Balance Deposit Interest Pd New Balance

In Row 9, set up these formulas (have to change some in row 10 later)
A9 =A4
B9 =A$3
C9 =(A9+B9)*(A$1/A$2)
D9 =Sum(A9:C9)

In row 10 we need to change the formula in column A to:
=D9
fill the other column formulas down from row 9 into row 10, and then fill
the formulas in all 4 columns as far down the sheet as you care to. You'll
see the $2000 exceeded in the 20th month.

If you have extra money (or are a little light one month) , simply type in
the actual deposit amount for the month in question, replacing the =A$3
formula in that cell in column B. Everything on down will adjust itself
automatically.

The accuracy of all of this is a little fuzzy - since most savings
institutions break it down to a daily interest rate based on the date of the
actual deposit. So you'd have to make the deposit on the same day each month
(1st) to get full interest. But the difference in depositing on the 1st,
5th, or even the 15th of a month in any given month probably isn't going to
change the end result "goal achieved month" by more than one month at worst.

 

[joeu2004]

I have a starting balance, the annual interest rate of my savings
account, and how much I can save each month. I also have a target
ammount in mind.

From this I want to know how many Months I will have to save this
before my Target is met.

Suppose your savings APY is 3%, you have $25,000 already, you plan to
save $750 per month, and your goal is $50,000. Then
the number of months is:

=roundup(nper(3%/12, -750, -25000, 50000), 0)

if I want to buy a home in 2 years
time, how much I'll have saved by then

=rounddown(fv(3%/12, 24, -750, -25000), 0)

And you might want to know how much you would have to save each month
in order to meet your goal:

=roundup(-pmt(3%/12, 24, -25000, 50000), 0)

my answers are coming out negitive

First, some people would say that the negative results are not
necessarily wrong. But like you, I like my results to be positive.

In this case, the key is to think of the "present value" (starting
balance) and "payments" (monthly amount saved) as outflows (negative)
and the "future value" (goal) as an inflow (positive). Then adjust
the sign of the result to be positive as you choose. In this case,
only the result of the PMT function needs to be changed.


Notes:

1. Computing the monthly interest rate as 3%/12 is an estimate.
First, since I am talking about the APY, the monthly rate should be
computed by (1+3%)^(1/12)-1. But the difference in this case is small
-- only $1 per month. Second, interest in some (not all) savings
accounts compound daily. But again, the difference is small. On the
other hand, many savings accounts compute simple interest based on a
daily balance or average daily balance; in that case, compounding is
indeed on a monthly basis.

2. I choose between ROUNDUP and ROUNDDOWN in to yield the more
conservator answer, depending on what is computed. Again, the
difference is not large enough to really make much of a difference.

3. You might want to use the after-tax interest rate. That is the APY
times 1-t, where "t" is sum of the applicable federal and state tax
rates.