PPMT Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the Excel PPMT Function in Excel to calculate the principal payment amount of an investment.

PPMT Function Overview

The PPMT Function Calculates the principal payment.

To use the PPMT Excel Worksheet Function, select a cell and type:

 

 

(Notice how the formula inputs appear)

PPMT Function Syntax and Inputs:

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

rate – The interest rate for each period.

nper – The total number of payment periods.

pv – The present value of the investment that the future payments are worth now.

fv – OPTIONAL: The Future Value of the investment or loan at the end of the number of payment periods.

type – OPTIONAL: The type argument shows when the payments are made, either at the end of the period by 0 or at the start of the period by 1. Its default value is 0.

 

What is the Excel PPMT Function?

The Excel PPMT function returns the principal payment of a loan or an investment for a given period based on the constant interest rate and periodic payments.

Calculate 3rd Principal Payment of a loan

Let’s find out the third month’s principal payment on a loan of $10,000 with an annual interest rate of 6%. The installments for the loan are made at the end of every month. And the complete loan has to be paid off in the next two years.

As the payments are made monthly, the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 6% (annual interest rate) / 12 (months per year) = 0.50%

and the number of payments per period is converted into the monthly number of payments by

NPER – 2 (years) * 12 (months per year) = 24

The Formula used for the calculation of principal payment is:

=PPMT(D7,D8,D9,D10,D11,D12)

 

The monthly payment of the loan is

PPMT = -$397.15

The result came in negative value because it shows that the cash is being paid out from the individual account.

Calculate 14th Principal Payment of a loan

Now let’s calculate the 14^th principal payment of an investment, whose current value is $0. But its future value is $100,000. The investment is to be held for the next six years. Its annual interest rate is 10% and the payments are made at the start of every quarter.

The payments are made every quarter, so the annual interest rate is converted into quarterly interest by

Quarterly Interest Rate – 10% (annual interest rate) / 4 (quarters per year) = 0.50%

and the number of payments per period is converted into the quarterly payments by

NPER – 6 (years) * 4 (quarters per year) = 24

The period of the principal payment, of the investment, to be found is

The Formula to use is:

=PPMT(D7,D8,D9,D10,D11,D12)

The Principal Payment value calculated from the PPMT function is

PPMT = -$4,157.43

Additional Notes

Make sure the units of nper and rate are consistent, e.g., in case of monthly interest rate, the number of periods of the investment should also be in months.

In the Financial Functions the cash outflows, such as deposits or other outgoing payments, are represented by negative numbers and the cash inflows, such as dividends, are represented by positive numbers.

#NUM! Error occurs when the value of the per argument is not between 1 and the value of nper argument.

#VALUE! Error occurs if any of the argument value is non-numeric.

Return to the List of all Functions in Excel

 

PPMT in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

PPMT Examples in VBA

You can also use the PPMT function in VBA. Type:

application.worksheetfunction.ppmt(rate,nper,pv,fv,type)

For the function arguments (rate, etc.), you can either enter them directly into the function or define variables to use instead.