How to Calculate Principal and Interest on a Loan in Excel

To calculate Principal based on a loan, we need to execute Excel’s PPMT function, and to calculate Interest according to a loan amount, we need to apply Excel’s IPMT function. In this article, you will learn how to calculate principal and interest based on a loan taken in Excel.

PPMT Function in Excel to Calculate Principal

The PPMT function returns the calculated value of the principal amount of a given amount (e.g. total investments, loans, etc.) for a given period of time.

Purpose

To calculate the principal of a given investment.

Syntax

=PPMT(rate, per, nper, pv, [fv], [type])

Return Value

The principal value of a given amount.

IPMT Function in Excel to Calculate Interest

The IPMT function returns the calculated value of the interest amount of a given amount (e.g. investments, loans, etc.) for a given period of time.

Purpose

To calculate the interest of a given investment.

Syntax

=IPMT(rate, per, nper, pv, [fv], [type])

Return Value

The interest value of a given amount.

Parameter Description

The parameters inside both of the functions are the same.

Parameter	Required/ Optional	Description
rate	Required	The constant interest rate per period.
per	Required	The period for which the required value should be calculated.
nper	Required	The total number of payment periods for the given amount.
pv	Required	The present value or the total value for all types of payments. Must be entered as a negative number. If omitted, it is assumed to be zero (0).
[fv]	Optional	The future value, meaning the desired cash balance after the last payment. If omitted, it is assumed to be zero (0).
[type]	Optional	Indicates when payments are due with the number 0 or 1. 0 = Payment is due at the end of the period. 1= Payment is due at the beginning of the period. If omitted, it is assumed to be zero (0).

Calculate the Principal and Interest on a Loan in Excel

In this section, you will learn how to calculate principal with the PPMT function and interest with the IPMT function based on a loan taken in Excel.

From the above scenario, we have some data in our hands to calculate the Principal and Interest for a given loan for a given period of time.

Given data,

Loan Amount -> $5,000,000.00 -> Given loan amount. So this is the first parameter, pv, for the functions. It must be entered as a negative value.
Yearly Rate -> 10% -> 10% interest rate should be paid annually.
Period per Year -> 12 -> There are 12 months in a year.
Period -> 1 -> We want to get the result for the first month, so stored 1 as the input data. This value is inconstant. So we now have the second parameter, per.
Total Period(year) -> 25 -> The total loan amount has to be paid in 25 years.
Future Value -> 0 -> No required future value, so set the [fv] parameter 0.
Type -> 0 -> We want to calculate the payment which is due at the end of the period. This is the last [type] parameter.

Now we can see that we still need two more parameters, rate and nper, to calculate the principal and interest value based on the loan given. And we can easily extract those parameters’ results just by simple mathematical calculation with the given data that we have already.

To calculate the Rate per Period, we can divide the Yearly Rate (10% in Cell C6) by the Period per Year (12 in Cell C7).

rate = Yearly Rate/ Period per Year = Cell C6/ Cell C7 = 10%/12 = 0.83%

To calculate the Number of Periods, we have to multiply the Total Period (25 in Cell C10) with the Period per Year (12 in Cell C7).

nper = Total Period*Period per Year = Cell C10*Cell C7 = 25*12 = 300

So now all the parameters for our PPMT and IPMT functions are in our hands.

rate = 83% -> Cell C8
per = 1 -> Cell C9
nper = 300 -> Cell C11
pv = -$5,000,000.00 -> Cell C5
[fv] = 0 -> Cell C12
[type] = 0 -> Cell 13

Now we can easily place these input values inside our formula and extract the results.

To get principal, write the following formula and press Enter.

=PPMT(C8,C9,C11,-C5,C12,C13)

You will get the principal amount of the loan given.

To get the interest, write the following formula and press Enter.

=IPMT(C8,C9,C11,-C5,C12,C13)

You will get the total interest of the loan provided.

Things to Remember

The period of interest is referred to as the parameter, per. It must be a numeric value from 1 to the total number of periods (nper).
The argument, rate, must be constant. For instance, if the annual interest rate is 7.5% for a 10-year loan, then calculate it as 7.5%/12.
By rules, the argument pv has to be entered as a negative number.

Download Practice Workbook

You can download the free practice Excel workbook from here.

Calculate Principal and Interest on a Loan.xlsx

Conclusion

This article explained in detail how to calculate principal and interest on a loan in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!