Home Archives for Financial Functions in Excel
April 9, 2011 Leave a Comment

Functions to Analyze Loan Payments in Excel

Loan Payment Functions

Loan Payment Functions

When you use the PMT Function in Excel, the result is the amount of money that you will pay each period (usually monthly) until the loan is repaid. When you want to know how much of each payment is an interest charge, you use the IPMT Function. And,to find the amount that goes towards reducing the principal on your loan, you use the PPMT Function.

Cumulative Payments

Two other functions are helpful in analyzing your loan payments. When you want to see the Cumulative amount of Interest (CUMIPMT Function) that you paid between two periods – e.g. Starting with payment 1  and ending with payment 12. You use the CUMPRINC Function to total the amount of your loan payments that went towards reducing the principal on your loan between any two periods.

Absolute and Relative Cell References

As you will see in the video, I use a combination of Absolute Cell References (e.g. $A$4) and Relative Cell References (e.g. A4) in these formulas. An Absolute Cell Reference means that cell will “remain in place” when the formula is copied down or across to other cells. In this example, I have my Loan Information (Amount to borrow, Interest Rate, etc.) at the top of the spreadsheet. Almost all references to these cells in formulas is Absolute.

Function Arguments Dialog Box

I almost always use the Function Arguments Dialog Box when I am creating my formulas. I use the Keyboard Shortcut, Ctrl + A at this point in the formula – “=PMT(” to activate the dialog box. The advantages of using the Function Arguments Dialog Box are:

  • Explanation of both the function and each “argument” in the function
  • Ensures arguments are answered in the proper sequence
  • Ensures that all “required” arguments (Argument Labels are in Bold)
  • Evaluates the result of each “intermediate” calculation – to the right of each argument

This is the first of several Excel Video Lessons that I am creating to demonstrate how to perform Financial Calculations. Let me know what you think or send me your questions to answer. You can add a comment below this post.

Watch This Video in High Definition on YouTube

Follow this link you view this Excel Video on my YouTube Channel – DannyRocksExcels

Download Excel Workbook

Follow this link to download the Excel Workbook I used in this lesson.

Subscribe to My Video Podcast

I invite you to receive an automatic notice – via email – whenever I publish a new Excel Video Lesson. Follow this link to find out the details. The subscription is free; you do not need to provide and personal information beyond your email address; and you can cancel at any time – no questions asked!

Functions to Analyze Loan Payments in Excel [ 8:05 ] Play Now | Play in Popup | Download (1170)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Financial Tools, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , ,
October 18, 2008 2 Comments

Excel Tells You the Future Value of Your Investment

Hey, want to know what your investment will be worth in 5 years? 10 years? Want a sure thing? Excel correctly calculates the future value of any investment! Provided …

  • You make a series of regular investments – even if it is just one “lump sum.”
  • The interest rate that you earn is “constant” – that is a “fixed annual interest rate.”

OK, so now you understand that neither I nor Excel are “touting sure thing” winners in the stock market. No one can do that. But, you can’t beat Excel when it comes to accurate – and easy to use – financial calculations.

In this video lesson, I demonstrate how to use both the =FV() and =PMT() Functions to calculate Future Values. I also show you how to perform “What-If” Analysis using a One-Input Data Table.

Here are the steps to follow in this Excel Video Lesson:

  1. Input values for Interest Rate (RATE), Number of Periods (NPER) and your Investment (PMT). These are the three required arguments for the =FV() Function. Use the Function Argument Dialog Box to ensure that you use the correct syntax for this function.
  2. One of the “optional” arguments is “Type.” If you omit this, Excel defaults to a “0” which means that you will make your investment contribution at the end of each period. A “1” means that the contribution is made at the beginning of each period. Over a number of years, this can make a significant difference in the “Future Value” of your investment.
  3. FV is also an “argument” in the =PMT() Function. If you want to calculate how much you need to contribute each month to reach a Savings Goal, use the =PMT() Function. However, in this case, the FV – while showing as an “Optional Argument” will refer to the cell containing your Savings Goal.
  4. Create a One-Input Data Table to perform “What-if” Analysis – e.g. to substitute a series of different interest rates in your =PMT() – or =FV() Function.

NEW! Download the Practice File for this Excel Video Lesson:

Find the Excel Video Lesson you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, What-if Analysis Tagged: , , , , ,