In the previous tutorial we looked at how to determine loan payments, both manually and by using the built-in PMT function. Now we will look at a few more of the Financial functions that you might want to use.
This is just a re-arrangement of the formula we used to calculate the payment, and has all of the same variables involved. But instead of solving for the payment, we use the payment to solve for how much it is worth. The best example of this sort of this is a winning lottery ticket. If you should ever be so fortunate as to win a lottery, you may be told that you won, for example, one million dollars. Sounds good, doesn’t it? Then you get to the fine print, and discover that this is 50,000 per year for 20 years. Well, multiply 50,000 by 20, and you get a million, to be sure, but what did you really receive? This function lets you calculate what the equivalent is in money you get today as a lump sum. So select this formula, click Next, and the data you need to enter is:
Rate – Here I would put in the rate of return you could get if you got the money today in one lump sum and invested it. For example, if you have money in a mutual fund what is the annual rate of return on investments there? You can often get this information by doing a little online research, such as Yahoo Finance.
NPER – Number of periods. If you are using an annual rate of return, this should be the number of years (20, in our example). If the payments are monthly over the 20 year period, put in 240, and make sure you adjust the rate of return to get a monthly average.
PMT – This is the amount you receive each period. If it is annual our example would be 50,000. If you are getting monthly checks instead, enter 4,166.67
Note that these fields are in bold, which means you need to enter them for the function to work. Any fields not in bold are optional.
If you run the numbers using annual payments of 50,000, the answer is 456,427.28. But if you get monthly payments the answer is 463,103.98. The logic here is that with a check coming in each month you can invest the money during the year and get a slightly better return than with one payment at the end of the year. Remember, the default assumption is end of the year, if instead you get it at the beginning of the year, put a “1” in the Type field, and now your total jumps to 497,505.74. The other thing to note is that the number comes back as negative. In Finance, that is just a short-hand way of noting the direction of the cash flow. This function is also used to calculate what the present value of a stream of payments you make. So don’t worry about the negative.
Just another re-arrangement of the same formula. If I put aside a sum of money each year, how much will I have at Retirement? Assume a monthly amount of 100, and that you have 40 years before you retire. As in the above example, find the average rate of return for your investment. In my example, I assume the same 9% annual as the above example.
PMT = 100
Rate = .09/12 = .0075
NPER = 40*12 = 480
And the answer (again, it is negative, but disregard that) is 468,132.03. So if you wanted to retire with a million, this won’t get it done. So what would you need to do? Well, the PMT function can do that. Using the same Number of periods and the same rate of return, I can use the PMT function with one million as my future value, and solve for the monthly amount. This is slightly different use of the function, but the adjustments you need to make are simple. In the Present Value field enter a zero to assume you start with nothing, and in the Future Value field, enter 1,000,000. Note that the Future Value field is considered optional by the function, but you need to use it to get this done. Enter all of this, and you get the answer of 213.61.
But suppose you have a head start. Your maiden Aunt Matilda has passed away and you are her heir to the tune of 10,000. Just use the same formula, but instead of putting in a zero for Present Value, put in the 10,000 you received as a bequest. (Note, you need to make this minus 10000 for the formula to work). Now when you run the formula you will get 136.48. The logic here is that you are investing that 10000 for 40 years and just letting the returns compound, so you don’t need to add as much to it.
This is even simpler. This function is for the question of how long you need to leave a quantity of money invested to get a given final amount. Let’s begin with Aunt Matilda (May she rest in peace) and her bequest of 10,000. How long would it take to get to one million at the same 9% rate of return? Assume we get our returns compounded monthly, and we have
Rate = .0075
Present Value = 10,000
Future Value = 1,000,000
Number of months required = 616.32
And dividing that number by 12 gives the years, which is 51.36.
Mortgage Payment Schedule
A problem that many people might find interesting concerns a home mortgage. Generally, you borrow a sum of money to purchase a home, and you have fixed monthly payments. Part of each months payment goes to interest on the loan, and part to principal. Because in the early years so much principal is still owed, most of the monthly payment goes to paying interest on the mortgage. But as you pay it down the proportion gradually shifts so that in the last years most of each payment goes to principal. In the United States, mortgage interest is a tax deduction, so homeowners have a reason to be very interested in the amount of interest they pay. The IPMT function is designed to tell you that, and it is very simple. The variables are ones we all have seen.
Rate = Interest rate on the loan, calculated per period (generally, monthly)
Period = Which period are we looking at? This number starts at 1, and goes as high as the length of the loan
NPER = Number of Periods, calculated as months in this case.
Present Value = The amount you borrowed.
Future Value = The amount you have left to pay at the end of loan. Generally, this should be zero.
Type = Indicates whether payment is at beginning of month (1) or at end of month (0, or left blank)
And there is a corresponding function that gives you the principal payment, PPMT, and it has all of the same arguments. Put them together and you can easily create Mortgage Repayment Schedule.
First, for the arguments we need I will use the same principle I used in the Simple Savings Model, and place these in their own section off to the right. In mine I put in the Annual Rate, then calculated the Monthly Rate as an intermediate calculation. Then I added NPER, and assumed a 20 year loan which means 240 monthly payments. For the amount borrowed I put in 100,000, and for future value I put in zero. Then I set up my schedule as follows:
I select cells A1 through D1, then Merge and Center Cells. I give them a blue background, set the font for Arial 12 Bold, and type “Mortgage Schedule:
Then I select cells A2 through D2, but I don’t merge them. I set the background to salmon.
In Cell A2 I type “Period”
In Cell B2 I type Interest Amount
In Cell C2 I type “Principal Amount
In Cell D2 I type “Total Payment
Then in Cell A3 I enter the number 1, and then click-and-drag through the A column until I have 240 periods which I will fill out.
In Cell B3 I enter the IPMT function. Now, when I fill this out I need to think ahead a little. I plan to click-and-drag to fill this column, and that means I need to be careful to give absolute cell references, which is done by using the $ character. My Loan Terms assumption are in the H column, so I fill out this function as follows:
Rate = $H$3
Period = A3. Note that this one does not use an absolute cell reference. I want this to increment when I click-and-drag to fill the column.
NPER = $H$4
PV = $H$5
FV = $H$6
Type = 0, which indicates a payment at the end of each month. Since you generally have one month between the time the loan is signed and the time your first payment is due, this is the most realistic
Then, in Cell C3 I enter the PPMT function, and fill it out exactly the same way as above for IPMT.
Finally, in Cell D3 I add them together to get the total monthly payment, =B3+C3. Again, do not use absolute cell references here since you want these to increment as you fill the column. Then click-and drag this column as well. The result is that you have a monthly payment of 836.44. Of this, you start out with an interest payment of 666.67, but by the end the interest payment is 5.54.
- One of the things you can do is to modify the values in your spreadsheet and get new values, if you set it up correctly. You could theoretically just enter numbers in your function and see what the result is, but if you first put that data on your spreadsheet and use cell references, you can easily change the data in the cell and instantly get an updated result
- This is another good reason to do Intermediate Calculations, since you can store the result in a cell and when you update the base number everything recalculates.
- The examples we have used all involve similar variables, and that turns out to be true of a lot of these Financial functions. Once you have learned one or two it is pretty easy to pick up the others
- Start by looking at the function, see what arguments it requires, and then gather the data. As in so many things in life, preparation is more than 50% of the job
- The functions I have used were selected for general interest to a lay audience. Many of the other Financial functions are of specialized interest to accountants or to investors. If you are one of these people you probably know all of this anyway, but the principles we have discussed will keep you in good shape as you use these other functions.
The sample spreadsheet for this tutorial with all examples can be found here.
Listen to the audio version of this post on Hacker Public Radio!