How to Calculate Principal and Interest on a Loan in Excel

PPMT Function in Excel to Calculate Principal

The PPMT function calculates the principal of a financial product or service  (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])

 

IPMT Function in Excel to Calculate Interest

The IPMT function calculates the interest portion of a financial product or service (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])

 

Function Parameters

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

Read More: How to Calculate Interest in Excel with Payments


Calculate the Principal and Interest on a Loan in Excel

Dataset on How to Calculate Principal and Interest on a Loan in Excel

  • Loan Amount -> $5,000,000.00 -> The loan amount. 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 -> To get the result for the first month, the input is 1. This value is variable.
  • Total Period(year) -> 25 -> The number of years allowed to pay off the total loan amount.
  • Future Value -> 0 -> No required future value, so the [fv] input is 0.
  • Type -> 0 -> To calculate a payment that is due at the end of the period, the input is 0.

In the above example, there are two parameters to find before calculating the principal and interest—rate and nper.

To calculate the Rate per Period (rate), divide the Yearly Rate (10% in this case) by the Period per Year (12).

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

To calculate the Number of Periods (nper), multiply the Total Period (25) by the Period per Year (12).

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

All parameters are now available.

  • 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
  • To calculate the principal, select the appropriate cell and enter the following formula:
=PPMT(C8,C9,C11,-C5,C12,C13)
  • Press Enter.
  • To calculate interest, select the appropriate cell and enter the following formula.
=IPMT(C8,C9,C11,-C5,C12,C13)
  • Press Enter.

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.
  • The argument pv has to be entered as a negative number.

Download Practice Workbook

You can download the free practice Excel workbook from here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo