Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

How to Use the PMT Function in Excel: Video Tutorials with Examples

Admin
Duration: 18:02
Submitted: 2 months ago
Views: 114

Comments (0)

Link to this video:

Description:

Do you want to learn how to use the PMT function in Excel? This video tutorial series will show you how!

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

In this video, we will walk through many examples of how to use the pmt function, with different inputs given so that you can understand how it works. Stay tuned for more videos in this series!

Table of Content;

00:00 INTRODUCTION

01:22 PMT Function

06:33 Mortgage Payment Using PMT Function

10:51 Annuity Payment Using PMT Function

14:02 PPMT Function (bonus)

16:02 IPMT Function

17:40 Conclusion

PMT Function in Excel

First thing first lets discuss syntax of PMT Function in detail;

=PMT(rate,nper,pv,fv,[Type])

Where;

Rate is the interest rate for the loan.

Nper is the number of payments for the loan.

Pv is the present value of the loan.

Fv is the future value of the loan.

Type is an optional argument that tells Excel how to calculate payments. The default setting is 0, which calculates payments based on end-of-period balances. A value of –l specifies payments based on beginning-of-period balances.

Mortgage Payment Using PMT Function

 

Lets say, we have a mortgage loan with following details;

Monthly Interest Rate = 0.005 (0.05 / 12)

Number of Payments over the Years = 360

Present Value of Loan $100000

Future value $0 as its final payment on maturity date and last but not least pmt function calculates periodic payment. So our equation will be;

=PMT(0.005, 360, 100000, 0)

Which gives us the result of $483.47 as monthly mortgage payment.

Annuity Payment Using PMT Function

Now lets take another example where we have an annuity loan with following details;

Monthly Interest Rate = 0.005 (0.05 / 12)

Number of Payments over the Years = 360

Present Value of Loan $100000

Future value $200000

Again pmt function calculates periodic payment so our equation will be;

=PMT(0.005, 360, 100000, 200000)

Which gives us the result of $288.24 as monthly annuity payment.

PPMT Function (Bonus)

Now lets say you want to calculate only principal amount that’ll be carried at each pmt period then PPMT function will help;

=PPMT(0.005, 360, 100000)

Which gives us the result of $416.67 as monthly pmt carrying only principal amount.

IPMT Function

Now lets discuss about IPMT function which is used to calculate interest payment for a given period;

=IPMT(0.005, 360, 100000)

Which gives us the result of $66.80 as monthly pmt carrying only interest amount.

Conclusion

So these are all the basics about PMT function in Excel with some examples to make you understand it better. In next video we will discuss about Type argument value and its effects on calculation. Till then stay tuned and happy learning!