New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

PPMT and IPMT: Calculating principal and interest per loan payment

From: Excel 2010: Financial Functions in Depth

Video: PPMT and IPMT: Calculating principal and interest per loan payment

Calculating the amount of principal you've paid on a loan lets you determine the amount of equity you have in your purchase. Your equity is simply your down payment plus any loan principal paid, plus appreciation of the asset. Calculating the interest paid on a loan lets you find the amount of interest you pay in the year, which you can often right off in your tax return. I have set this workbook up so that we can calculate the principal component and interest component of an individual payment. The PPMT function, as you probably guessed, lets you calculate the principal and IPMT the interest.

PPMT and IPMT: Calculating principal and interest per loan payment

Calculating the amount of principal you've paid on a loan lets you determine the amount of equity you have in your purchase. Your equity is simply your down payment plus any loan principal paid, plus appreciation of the asset. Calculating the interest paid on a loan lets you find the amount of interest you pay in the year, which you can often right off in your tax return. I have set this workbook up so that we can calculate the principal component and interest component of an individual payment. The PPMT function, as you probably guessed, lets you calculate the principal and IPMT the interest.

So let's go through the arguments. The Rate is exactly what you would expect, the annual interest rate, and Period Identifies the period for which you want to identify the principal and interest components. Number of periods is the number of payments for the loan. In this case it's 360 which is 30 years of 12 monthly payments. Then there is Present value of $750,000. Now it's a negative number because it's an amount that you owe, then Future value is the amount that you want to end up either owing or having in surplus and in this case it's 0.

And then Type can be either 0 or 1 and that indicates whether you pay at the end of a period, which is 0 which is the default value, or at the beginning of a period, which is 1. With those arguments in place, let's go ahead and create the formulas. So we have the Principal component, which is =ppmt(, and then we have the rate. That is in cell c3, but because we're making monthly payments we need to divide that by 12, and then the period for which we want to perform our calculations is in cell c4.

Then the number of periods is in cell c5, present value is c6, and then you see that the future value and type arguments are in square brackets and the square brackets means that those arguments are optional. Excel will fill in default values if you don't put them in, but in this case our worksheet contains the values we need. So I'll go ahead and type c7, comma, and then c8, indicating that the payment comes at the end of the period. Type a right parenthesis. Just make sure everything is okay. It's especially important that we divide the interest rate by 12 which we are.

So I press Enter and we see that our principal component of the first payment is about $729. Now I'll move down using my keyboard to cell c13 and I create the IPMT function. And I'll go through this more quickly, because it uses exactly the same arguments. So we have IPMT and then the rates' in c3, again dividing by 12. Period is in c4. And then we have the number of periods. That's in c5. Present value c6, future value c7, and the type in c8.

Everything looks good. I'll press Tab so I don't scroll down and we see that the interest component is over $3800. Over the life of a loan, the principal component and interest component will change. You pay more interest at the beginning of the loan and more principal toward the end, even though individual payment amounts never vary. So for example, let's change the Period from 1 to 120 and that would be after 10 years of the 30 year loan.

So I type 120 into cell c4 and press Enter and we see that the principal component has gone up and the interest component has gone down fairly substantially. Now I'll move back up to cell C4 and type in 180 which is exactly the midpoint of the loan. See the principal is catching up, but it's not quite there to the interest. Then I'll change that period in c4 to 240 and we see that we're now paying off more principal interest than interest. And then for the last loan period, which is 360, we see that we are paying almost exclusively principal and practically no interest at all.

Calculating the amount of principal and interest you have or will pay off on your loans reflects both the equity you have in your purchase and the amount of money you can write off on your taxes. Always be sure to watch your interest expenses closely.

Show transcript

This video is part of

Image for Excel 2010: Financial Functions in Depth
Excel 2010: Financial Functions in Depth

52 video lessons · 13172 viewers

Curt Frye
Author

 
Expand all | Collapse all
  1. 2m 11s
    1. Welcome
      1m 6s
    2. Using the exercise files
      36s
    3. Disclaimer
      29s
  2. 28m 32s
    1. PMT: Calculating a loan payment
      3m 31s
    2. PPMT and IPMT: Calculating principal and interest per loan payment
      4m 18s
    3. CUMPRINC and CUMIPMT: Calculating cumulative principal and interest paid between periods
      4m 30s
    4. ISPMT: Calculating interest paid during a specific period
      2m 13s
    5. EFFECT and NOMINAL: Finding nominal and effective interest rates
      3m 31s
    6. ACCRINT and ACCRINTM: Calculating accrued interest for investments
      4m 15s
    7. RATE: Discovering the interest rate of an annuity
      2m 41s
    8. NPER: Calculating the number of periods in an investment
      3m 33s
  3. 19m 5s
    1. SLN: Calculating depreciation using the straight-line method
      1m 48s
    2. DB: Calculating depreciation using the declining balance method
      3m 10s
    3. DDB: Calculating depreciation using the double-declining balance method
      3m 20s
    4. SYD: Calculating depreciation for a specified period
      2m 13s
    5. VDB: Calculating declining balance depreciation for a partial period
      3m 24s
    6. AMORDEGRC: Calculating depreciation using a depreciation coefficient
      2m 27s
    7. AMORLINC: Calculating depreciation for each accounting period
      2m 43s
  4. 22m 33s
    1. FV: Calculating the future value of an investment
      2m 48s
    2. FVSCHEDULE: Calculating the future value of an investment with variable returns
      2m 21s
    3. PV: Calculating the present value of an investment
      2m 6s
    4. NPV: Calculating the net present value of an investment
      3m 17s
    5. IRR: Calculating internal rate of return
      2m 33s
    6. XNPV: Calculating net present value given irregular inputs
      2m 32s
    7. XIRR: Calculating internal rate of return for irregular cash flows
      1m 48s
    8. MIRR: Calculating internal rate of return for mixed cash flows
      2m 2s
    9. DISC: Calculating the discount rate of a security
      3m 6s
  5. 24m 12s
    1. COUPDAYBS: Calculating total days between coupon beginning and settlement
      3m 2s
    2. COUPDAYS: Calculating days in the settlement date's coupon period
      2m 48s
    3. COUPDAYSNC: Calculating days from the settlement date to the next coupon date
      3m 1s
    4. COUPNCD: Calculating the next coupon date after the settlement date
      2m 43s
    5. COUPNUM: Calculating the number of coupons between settlement and maturity
      2m 55s
    6. COUPPCD: Calculating the date of a coupon due immediately before settlement
      3m 4s
    7. DURATION: Calculating the annual duration of a security
      3m 20s
    8. MDURATION: Calculating the duration of a security using the modified Macauley method
      3m 19s
  6. 28m 43s
    1. DOLLARDE and DOLLARFR: Converting between fractional prices and decimal prices
      2m 36s
    2. INTRATE: Calculating the interest rate of a fully invested security
      2m 50s
    3. RECEIVED: Calculating the value at maturity of a fully invested security
      2m 46s
    4. PRICE: Calculating the price of a security that pays periodic interest
      3m 19s
    5. PRICEDISC: Calculating the price of a discounted security
      2m 48s
    6. PRICEMAT: Calculating the price of a security that pays interest at maturity
      1m 57s
    7. TBILLEQ: Calculating the bond-equivalent yield for a Treasury bill
      1m 50s
    8. TBILLPRICE: Calculating the price for a Treasury bill
      1m 31s
    9. TBILLYIELD: Calculating the yield of a Treasury bill
      1m 41s
    10. YIELD: Calculating the yield of a security that pays periodic interest
      2m 59s
    11. YIELDDISC: Calculating the annual yield for a discounted security
      2m 9s
    12. YIELDMAT: Calculating the annual yield of a security that pays interest at maturity
      2m 17s
  7. 12m 1s
    1. ODDFPRICE: Calculating the price of a security with an odd first period
      3m 17s
    2. ODDFYIELD: Calculating the yield of a security with an odd first period
      3m 3s
    3. ODDLPRICE: Calculating the price of a security with an odd last period
      2m 44s
    4. ODDLYIELD: Calculating the yield of a security with an odd last period
      2m 57s
  8. 1m 5s
    1. Additional resources
      1m 5s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.