reverse interest

[Tracy]

Hi what formula would I need for excel to calculate interest in reverse so
that I start with $900 withdraw $30 per month for 30 months so how much
would I have if the interest rate was 5% per year?
Thanks Tracy

 

[Niek Otten]

HI Tracy,

=FV(5%/12,30,30,-900)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi what formula would I need for excel to calculate interest in reverse so
| that I start with $900 withdraw $30 per month for 30 months so how much
| would I have if the interest rate was 5% per year?
| Thanks Tracy
|
|

 

[Bernard Liengme]

Look at Future Value (FV) in Help
Remember to use rate =5%/12 and for nper use 30 (months)
PV is you initial amount -900 (negative since you GAVE the money to the
bank)
PMT is +30 since (positive since you GOT the money)
I get $63.02 which agrees with an amortization table I made

0 900 ( 0 in A1, 900 in B1 - starting about a month zero)
1 =A1*(1+5%/12)-30 (interest after first month less
withdrawal)
2 =A2*(1+5%/12)-30
.....
30 =A30*(1+5%/12)-30

best wishes

 

[Gary''s Student]

Actually the interest is always going forward. Let's say that at the end of
the first month, monthly interest is first accured on the $900, and then 30
is withdrawn. At the end of the second month, monthly interest is accured on
the balance at the end of the first on and then 30 is withdrawn.

The two main ways of calculating the interest rate are:

YearlyRate/12
and
(1+YealyRate)^(1/12)-1

Let's look at both ways. In both B1 and B1 enter:
900

In A2 enter:
A1*(1+0.05/12)-30 and copy down

In B2 enter:
=B1*(1+0.05)^(1/12)-30 and copy down

We see:

900 900
873.75 873.6667114
847.390625 847.2261377
820.9214193 820.6778419
794.3419252 794.021385
767.6516832 767.2563264
740.8502319 740.3822237
713.9371079 713.3986325
686.9118458 686.3051068
659.7739785 659.1011988
632.5230367 631.7864587
605.1585494 604.3604349
577.6800433 576.8226741
550.0870435 549.1727211
522.3790729 521.4101187
494.5556523 493.5344081
466.6163009 465.5451284
438.5605355 437.4418168
410.387871 409.224009
382.0978205 380.8912382
353.6898948 352.4430363
325.1636027 323.8789328
296.518451 295.1984557
267.7539445 266.4011307
238.869586 237.4864819
209.8648759 208.4540313
180.7393129 179.3032988
151.4923934 150.0338026
122.1236117 120.6450589
92.63246007 91.1365818
63.01842865 61.50788352