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
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.
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.
|
Read More:Â How to Calculate Interest in Excel with Payments
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.
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.
Related Articles
- How to Calculate Gold Loan Interest in Excel
- How to Calculate Credit Card Interest in Excel
- How to Calculate Home Loan Interest in Excel
- How to Calculate Accrued Interest on Fixed Deposit in Excel
- How to Calculate Accrued Interest on a Bond in Excel
- How to Calculate Accrued Interest on a Loan in Excel
