Loan Maturity Date

[brandonmcg]

I am trying to figure out the Maturity Date on a Loan using First Payment Date, Payment Amount, Interest Rate and Loan Amount. We do not know the term of the loan just these factors.

</SPAN>	</SPAN>
</SPAN>	</SPAN>
</SPAN>	</SPAN>
</SPAN>	</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[bbott]

Take a look at the NPER function.

 

[joeu2004]

I am trying to figure out the Maturity Date on a Loan using First Payment Date, Payment Amount, Interest Rate and Loan Amount. We do not know the term of the loan just these factors.

Take a look at the NPER function.

But you also need to know: the frequency of payments (e.g. monthly); and how to convert stated interest rate to a periodic interest rate (per payment).

For the latter, there are 3 common methods: simple rate (US, AUS, NZ, others); compound rate (EU); and compounded monthly semiannually ("say what?!"; Canadian).

Also, if the payment amount is rounded (probably), NPER might not return an integer. Typically, round up.

Finally, NPER returns the number of payments, not a date. You will need to know how to calculate the date based on number of payments. For example, use EDATE if payments are monthly.

 

[brandonmcg]

US Dollars and the frequency of the payments is monthly and straight scheduled interest. The payment is fixed for the unknown term. We get loan modifications where the payment is amortized over an unknown term. t can be, 15, 20, 30, 40 or even fifty years or anywhere in the middle. We need the date to not to have to play with the numbes to back into the payment we are provided. What would the formula look like for this?

Payment 798.01, interest rate 4.25% interest start date 12/01/2014, first payment date 01/01/2015 UPB $184,034.60.

The term in months is what? What would the formula look like for this?
What is the maturity date? What would the formula look like for this?


Thank you!

 

[MickG]

Try:-
Based on Principle = 184034.6.Payments =798.01 Int=4.25% (0.003542/mth)

Number of Periods = n
n =Log(798.1/(798.1- 184034.6*.003542)/log(1+0.003542)
n = 480

Or:-
=NPER(0.003542,798.1,-184034.6)

 

[Dave Patton]

You can consider using the Round function and the formula may need an additional ")" before the
"/",

=ROUND(LOG(798.1/(798.1-184034.6*0.003542))/LOG(1+0.003542),0)

=ROUND(LOG(B4/(B4-B1*B2/12))/LOG(1+B2/12),0)

=ROUND(NPER(B2/12,B4,-B1),0)