Loan Maturity Date

brandonmcg

New Member
Joined
Jan 14, 2009
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
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>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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!
 
Upvote 0
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)
 
Last edited:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top