Sanjida Ahmed
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.
Download Workbook
You can download the free practice Excel workbook from here.
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
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
Return Value
The interest value of a given amount.
Read more: How to Calculate Interest on a Loan in Excel
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.
|
Similar Readings
- How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
- Calculate Interest Rate in Excel (3 Ways)
- Calculate Interest in Excel with Payments (3 Examples)
- How to Calculate Interest Between Two Dates Excel (2 Easy Ways)
Calculate the Principal and Interest on a Loan in Excel
In this section, you will learn how to calculate principal with PPMT function and interest with 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) with the Period per Year (12 in Cell C7).
rate = Yearly Rate/ Period per Year = Cell C6/ Cell C7 = 10%/12 = 0.83%
And 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.
- And 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.
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.
