Forum Discussion
Which Function
=pv, =fv, =pmt, =rate, =nper, =ipmt, =ppmt, =cumipmt, =cumprinc
mattyw wrote: ``my question limits me to the following functions and their respected inputs =pv, =fv, =pmt, =rate, =nper [etc]``
That sounds like a class assignment. So we should not provide a turnkey solution. But in the interest of correcting misdirection....
We can use NPER as follows:
=ROUNDUP(NPER(B2, B4, -B1, B1*40%), 0)
where B2 is the monthly rate, B4 is the monthly payment, and B1 is the original loan amount.
For example (and see the attached Excel file), that is the formula in B6:
The monthly payment in B4 is calculated by the formula =PMT(B2, B3, -B1) .
If the monthly payment is not rounded, we can use any amount for the original loan.
The amortization schedule in columns E:G demonstrate correctness. The formulas are:
E4: =E3+1
F4: =F3*(1+$B$2) - $B$4
G4: =F4 / $B$1
You're right this was the last question in a uni assignment, thank you so much I immediately got the correct answer following your method!
mattyw wrote: ``I immediately got the correct answer following your method!``
I should hope so! I'm sorry I gave you the answer. Did you learn anything from it?
Suppose we save $100 at the beginning of every month, with an initial balance of $10,000 and a monthly interest rate of 1%.
(a) How many months will it take to save at least 60% more?
(b) What is the balance at the end of the previous month?
Show your data and formulas. Demonstrate their correctness using an amortization schedule.
Hint: Use only the "core" financial functions: PV, FV, RATE, PMT, NPER. Do not use the "add-on" functions PPMT, IPMT, CUMPRINC and CUMIPMT, which would give wrong(!) results, BTW. The latter group of functions were contributed in earlier Excel versions. They were "mainstreamed" in Excel 2007 without sufficient testing (sigh).
