Calculate interest on an investment

[darkwing_duck]

I would like to create two amoritization schedules based on a money
market account that starts with $60,000 that I take $167 out of each
month for the next 30 years.

The first amoritzation schedule is based on account that pays (for
example) 2% annual interest, compounded monthly.

The second amoritization schedule is based on an account that pays
(for example) 0.24% interest each month.

TIA,
Robert

 

[James Ravenswood]

I would like to create two amoritization schedules based on a money
market account that starts with $60,000 that I take $167 out of each
month for the next 30 years.

The first amoritzation schedule is based on account that pays (for
example) 2% annual interest, compounded monthly.

The second amoritization schedule is based on an account that pays
(for example) 0.24% interest each month.

TIA,
Robert

In both A1 and B1, enter 60,000.
In A2 enter:
=ROUND( A1*(1+0.02/12), 2 )-167 and copy down
In B2 enter:
=ROUND( B1*(1+0.0024), 2 )-167 and copy down

We see:

$60,000.00 $60,000.00
$59,933.00 $59,977.00
$59,865.89 $59,953.94
$59,798.67 $59,930.83
$59,731.33 $59,907.66
$59,663.88 $59,884.44
$59,596.32 $59,861.16
$59,528.65 $59,837.83
$59,460.86 $59,814.44
$59,392.96 $59,790.99
$59,324.95 $59,767.49

 

[Jens]

You can also use these formulas:

=FV(0,0024;360;167;-60000)
=FV(0,02/12;360;167;-60000)

Jens

 

[joeu2004]

I would like to create two amoritization schedules based on a money
market account that starts with $60,000 that I take $167 out of each
month for the next 30 years.

The first amoritzation schedule is based on account that pays (for
example) 2% annual interest, compounded monthly.

The second amoritization schedule is based on an account that pays
(for example) 0.24% interest each month.

You subject line and your text body ask two different questions. You
can calculate the cumulative interest earned on an investment without
an amortization schedule, assuming a fixed interest rate as you have.

But in either case, before you can compute a solution for the first
case, you need to convert the annual rate to a monthly rate. That is
not as straight-forward as most people think. It depends on
jurisdiction (US, Canada, UK, etc). And it depends on what the "2%
annual interest" truly represents.

For example, in the US, annual rates for money market accounts are
usually expressed in terms of annual percent yield (APY), a compounded
rate, not a simple interest rate. My understanding is: the same is
true for the UK; but I don't know for sure. In that case, the monthly
rate would be RATE(12,0,-1,1+2%) or (1+2%)^(1/12), which are
equivalent.

(Canada might use an even more complicated way of expressing annual
rate. I hope we do not need to go there.)

On the other hand, if the "2% annual interest" is indeed the simple
interest rate, the monthly rate would be simply 2%/12.

Assuming the principal is in B1 (60000), the monthly withdrawal is in
B2 (167), the investment period in months is in B3 (30*12), and the
monthly interest rates are in B5 and D5 for the first and second cases
respectively, the cumulative interest can be computed as follows:

1. For the first case: =FV(B5,$B$3,$B$2,-$B$1)-$B$1+$B$3*$B$2

2. For the second case: =FV(D5,$B$3,$B$2,-$B$1)-$B$1+$B$3*$B$2

The use of absolute references (e.g. $B$3 instead of B3) is optional.
It might make it easier to copy the formula.

Note: Those formulas and the following formulas assume that the
withdrawal is at the end of each month.

If you still want amortization schedules, the following is a bare-
bones design.

Assume that B9:B368 and D9368 will be the interest earned each month
for the first and second cases respectively, and C9:C368 and E9:E368
will be the corresponding balance at the end of each month, after
adding interest and subtracting the withdrawal.

Then set up the following formulas:

B8, cumulative interest: =SUM(B9:B368)
C8, initial balance: =$B$1
B9, monthly interest: =B8*B$5
C9, ending balance: =C8+B9-$B$2
Copy B9:C9 down through B368:C368

D8, cumulative interest: =SUM(D9368)
E8, initial balance: =$B$1
D9, monthly interest: =E8*D$5
E9, ending balance : =E8+D9-$B$2
Copy D9:E9 down through D368:E638

Note the I purposely do not round the formulas in B9:E9. Interest
computation is one dollar-and-cents calculation that I do not round,
at least without more information, for several reasons.

1. It is easier to compare with results from financial functions like
FV, which do not round periodic amounts.

2. There are no rules (in the US) for when and how interest should be
rounded (up, down, etc). It is left up to the financial institution.

3. When to round depends on: (a) when interest is "paid" to the
account, which might not be the same as the compounding frequency; and
(b) whether the financial institution rounds interest when it is
"paid" to the account. Some do; some don't; and some round interest
only when it is reported for tax purposes, which may be quarterly or
annually depending on jurisdiction.

Usually, the "error" caused by rounding (or not) is relatively small.
For example, for the examples that you present, the difference is
$0.01 in the first case and $0.03 in the second case after 30 years, a
relative error of about 0.00004% and 0.00006% respectively.

Hope this helps.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups; hence, participation is limited to the
sites that share a common newsgroup mirrow, which is no longer
centralized at MS.

 

[joeu2004]

You can also use these formulas:
=FV(0,0024;360;167;-60000)
=FV(0,02/12;360;167;-60000)

Those formulas compute the final balances, not the cumulative
"interest on an investment", which is in subject line.

 

[joeu2004]

Errata....

For example, in the US, annual rates for money market accounts are
usually expressed in terms of annual percent yield (APY), a compounded
rate, not a simple interest rate. [....]   In that case, the monthly
rate would be RATE(12,0,-1,1+2%) or (1+2%)^(1/12), which are
equivalent.

The latter should be (1+2%)^(1/12)-1

 

[joeu2004]

Errata....

B8, cumulative interest:  =SUM(B9:B368)
C8, initial balance:         =$B$1
B9, monthly interest:     =B8*B$5
C9, ending balance:       =C8+B9-$B$2
Copy B9:C9 down through B368:C368

The formula in B9 should be =C8*B$5 (previous balance times monthly
interest rate).

(Usually I copy-and-paste formulas from the Excel worksheet to here.
I guess I neglected to do that in this case. Mea cupla!)