Which Function

Question

I need to find the correct function and formula to solve the following,

At the end of which month will it be for the loan outstanding balance to be less than 40% of the original loan amount.

Help would be greatly appreciated

riny_van_eekelen · Answer

mattyw&nbsp;Perhaps the attached example (picture and file) helps:&nbsp;

mattyw · Answer

Thanks for the response Ricky! unfortunately my question limits me to the following functions and their respected inputs=pv, =fv, =pmt, =rate, =nper, =ipmt, =ppmt, =cumipmt, =cumprinc

joeuser2004 · Answer

mattyw&nbsp; wrote: ``my question limits me to the following functions and their respected inputs =pv, =fv, =pmt, =rate, =nper [etc]``&nbsp;That sounds like a class assignment.&nbsp; So we should not provide a turnkey solution.&nbsp; But in the interest of correcting misdirection....&nbsp;We can use NPER as follows:&nbsp;=ROUNDUP(NPER(B2, B4, -B1, B1*40%), 0)&nbsp;where B2 is the monthly rate, B4 is the monthly payment, and B1 is the original loan amount.&nbsp;For example (and see the attached Excel file), that is the formula in B6:&nbsp;&nbsp;The monthly payment in B4 is calculated by the formula =PMT(B2, B3, -B1) .&nbsp;If the monthly payment is not rounded, we can use any amount for the original loan.&nbsp;The amortization schedule in columns E:G demonstrate correctness.&nbsp; The formulas are:&nbsp;E4: =E3+1F4: =F3*(1+$B$2) - $B$4G4: =F4 / $B$1

mattyw · Answer

JoeUser2004&nbsp;&nbsp;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!&nbsp;

joeuser2004 · Answer

mattyw&nbsp; wrote: ``I immediately got the correct answer following your method!``&nbsp;I should hope so!&nbsp; I'm sorry I gave you the answer.&nbsp; Did you learn anything from it?&nbsp;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.&nbsp; Demonstrate their correctness using an amortization schedule.&nbsp;Hint: Use only the "core" financial functions:&nbsp; PV, FV, RATE, PMT, NPER.&nbsp; Do not use the "add-on" functions PPMT, IPMT, CUMPRINC and CUMIPMT, which would give wrong(!) results, BTW.&nbsp; The latter group of functions were contributed in earlier Excel versions. They were "mainstreamed" in Excel 2007 without sufficient testing (sigh).

Forum Discussion

Which Function

5 Replies

Resources