Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

reverse interest

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tracy - 30 Mar 2008 21:00 GMT
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 - 30 Mar 2008 21:32 GMT
HI Tracy,

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

Signature

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 - 30 Mar 2008 21:43 GMT
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
Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> 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
Gary''s Student - 30 Mar 2008 21:48 GMT
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

Signature

Gary''s Student - gsnu200776

> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.