Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reverse interest
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reverse interest
HI Tracy,
=FV(5%/12,30,30,-900) -- Kind regards, Niek Otten Microsoft MVP - Excel "Tracy" wrote in message ... | 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 | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reverse interest
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tracy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
reverse interest
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 a 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 -- Gary''s Student - gsnu200776 "Tracy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reverse value | Excel Worksheet Functions | |||
reverse value | Excel Worksheet Functions | |||
Reverse Sum | Excel Discussion (Misc queries) | |||
to compute interest rate from principal and interest amount | Excel Discussion (Misc queries) | |||
Effective rate of Interest and Copounding Interest | Excel Worksheet Functions |