Forum Discussion

mattyw's avatar
mattyw
Copper Contributor
Dec 16, 2021

Which Function

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.   ...
excel
Excel on Mac
Formulas and Functions
  • mattyw's avatar
    mattyw
    Copper Contributor
    Dec 16, 2021
    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's avatar
      JoeUser2004
      Bronze Contributor
      Dec 16, 2021

      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

      • mattyw's avatar
        mattyw
        Copper Contributor
        Dec 16, 2021

        JoeUser2004 

         

        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!

         

Resources