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.


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

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

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.
  • 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).

Similar Readings


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.

Dataset on How to Calculate Principal and Interest on a Loan 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.


Further Readings

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo