# Loan Maturity Date

#### brandonmcg

##### New Member
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.﻿


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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### bbott

##### Well-known Member
Take a look at the NPER function.

#### joeu2004

##### Banned user
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

##### New Member
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

##### MrExcel MVP
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:

#### Dave Patton

##### Well-known Member
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)

Replies
0
Views
832
Replies
0
Views
891
Replies
8
Views
1K
Replies
2
Views
188
Replies
1
Views
1K

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby

### 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.

### Which adblocker are you using?

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

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