Future Value of an Annuity Lab
PMT Formula Lab
Present Value Lab
Present Value of an Annuity Lab
Time Value of Money
Overview
This lesson covers the topic of Time Value of Money and prepares students for lessons on simple interest loans (operating notes and lines of credit) and amortized loans. It introduces the ideas of present and future values, compounding and discounting, payements and time periods. It uses Microsoft Excel extensively as an aid for problem calculations.
Time Value of Money
Time Value of Money
When it comes to money, the bottom line is that a dollar today is preferred to a dollar in the future. Why you ask? Here are four reasons:
- If that dollar is spent on consumption, we would prefer to receive that enjoyment now. That is simply human nature. I don’t want to buy new clothes next month or next year, I want them now.
- That dollar could be invested with someone that needs that dollar now, where it would then earn interest. While I may not need to spend the money now, either for business or for consumption, someone else does, and they are willing to pay you rent on your money. So by delaying your satisfaction of spending that dollar, you are actually receiving a benefit in earning interest when you are paid back.
- Risk is a factor, in that unforeseen circumstances could prevent us from receiving the dollar in the future. Risk here is many places. As grim as it seems, you may be unable to spend the money in the future due to death or injury. Or it could come from investing the money with someone else, yes you are entitled to an interest payment, but that only happens if they are able to pay you back.
- Inflation may diminish the value of that dollar over time. The definition of inflation is too many dollars chasing too few assets. When this occurs, the dollar is the same, it simply purchases less stuff as a result. So inflation doesn’t change the dollar (it is still worth $1) as much as it changes the values of everything that dollar buys.
There are two types of values that we look at when examining the time value of money. The first is Present Value (PV) (I will always abbreviate teems with the syntax that Excel uses) which is simply the number of dollars available or invested at the current time or the current value of some amount to be received in the future. The second is Future Value (FV) which is the amount to be received at some point in the future or the amount a present value will be worth at some future date when invested at a given interest rate.
Other terms that are important to know and understand are as follows:
Payment (PMT) – The number of dollars to be paid or received in a time period.
Interest Rate (rate) – The interest rate used to find present and future values. It is quoted in percent and represents the value of return for renting your money out to someone else, or what you have to pay to rent money from someone else. Often when we are finding present values of a future income the interest rate is refereed to as a discount rate.
Time Periods (nper) – The number of time periods used to compute present and future values.
Annuity – A term used to describe a series of periodic payments.
A quick note about interest rates. The interest rate is a return on the amount of money, but it has to have a time period associated with it. You cannot have a 5% interest rate, unless you state how long you expect it to take to return to you 5% of the original value. In other words, an interest rate, always needs to have a time component to it. For this section, we will always assume the rates are APR – annual percentage rates (unless you are explicitly told otherwise). Meaning that a 5% APR, will return 5% of its initial value at the end of exactly 1 year:
- A woman loans her neighbor $1000 at an APR of 5%. She is expecting to receive the $1000 she initially loaned her, plus 5% of the value back at the end of the year. 5% * $1000 = $50. So she intends to receive the $50 interest payment as well as the original $1000 back at the end of the year.
- Rates can also me be monthly rates. If the woman in the previous example wanted to loan $1000 at an MPR (monthly percentage rate) of 5%, then she expects to receive the $50 interest payment and the $1000 original amount back at the end of 1 month. A 5% MPR Is equivalent to a 60% APR, since 5% MPR * 12 months = 60% APR (we even use unit cancelling in finance!).
- Rates can also be weekly rates, daily rates, or any variation of the calendar that you can think of. The key is always this – the rate and the time always need to be in the same unit. In other words, if the time period is in years, the rate must be in years. If the rate is in days, the time period must be in days.
It is also important to understand that the direction we are heading from a present value to a future value or from a future value to a present value dictates whether we are compounding or discounting. In short, when we are moving from a present value (what we have today) to a future value (what it will be worth in a year) we are compounding the present value to arrive at the future value. When we know what we will have in the future and want to know what it is worth today, we are discounting back to the present. The image below helps to explain:
Future Value
Future Value Calculations
The image below represents the visual of what it means to go from a PV to an FV. Over time, because of the time value of money, the present value grows into a larger future value. In the section that follows you will learn the math, as well as tools that can be used to do these calculations.
When we work on FV problems, we know what the present value is. Essentially, we know what we have today, what we need to calculate is what that present amount will be worth in the future. Imagine we currently have $1000 and we want to know what that $1000 will be worth if it were to earn 8% APR for 1 year, 2 years and 3 years? There are actually multiple methods for answering these, so we will go through each one individually.
Table:
First we can create a table that calculates the interest earned each year – year by year. It is somewhat tedious and time consuming, but it is important to understand what is going on before learning to use the tools that easier and faster. The key here is that every year is just a simple interest problem. Simple interest means that you are only calculating the interest earned for 1 time period. In this case 1 year. See the table below:
| Year | Value at beginning of year ($) | Interest rate (%) | Interest earned ($) | Value at end of year ($) |
| 1 | 1000.00 | 8 | 80.00 | 1080.00 |
| 2 | 1080.00 | 8 | 86.40 | 1166.40 |
| 3 | 1166.40 | 8 | 93.31 | 1259.71 |
If we break down the table above, you will se that it is pretty simple math. In year 1 we start with $1000 and it earns 8% APR interest. Since this is a simple interest problem, you only need to take $1000 * 8% = $80. That is why the interest earned is $80. So at the end of the 1st year, the value is now $1080, since the original $1000 + $80 (interest earned) = $1080. Since $1080 is the value at the end of the 1st year, it stands to reason that $1080 will be the value at the start of the 2nd year, so the process starts all over again, only this time the formula for interest earned in year 2 is $1080 * 8% = $86.40. That interest earned is added to the value that you started with of $1080 + $86.40 = $1166.40.
That right there is what is known as compound interest. Compound interest is the act of earning interest on an investment and then having the interest earned start earning interest itself. We only ever invested $1000, but since we left the original $80 of interest earned in the investment, the next year we start earning even more interest. If we would have taken the initial interest payment out (in other words pocketed the $80 interest payment, in year 2 we would have only had $1000 earning 8% interest.
The tables are good, and they are important to understand for the remainder of the section on finance, however, for most cases of determining the future values and present values, they are lengthy, cumbersome and take too much time. Lucky for us, there are 2 other ways that are easier to use and much faster.
Formula:
The mathematical formula for computing the future value of an investment is as follows:
FV = PV (1 + i)ⁿ
where FV = future value, PV = present value, i = interest rate and n = time periods or nper
To calculate our problem from earlier, e plug in our information into the formula to get:
FV = $1000 (1 + 0.08)3 => FV = $1000 (1.08)3 => FV = $1000 * 1.25971
FV = $1259.71
As you can see, we get the same answer for the final year three future value in the table as we do when use the mathematical formula. And lets be honest, given the calcualtors that we have available today, working math problems to powers is not that difficult. The problem above is easily calculated on a simple calculator on your computer by using the xy button on your calculator. First you type in the value for x, in our case 1.08, then you hit the xy button on your calculator and then finally hit the number of years, in our case 3. Once you get your answer of 1.257912 (to be exact) multiply that by the present value which was $1000.
Excel:
The last and simplest method for calculating the future value of an investment, is to use excel, which is essentially a really powerful calculator that also organizes data as well as whole host of other things! Utilizing excel is quite simple, but you do have to familiarize yourself with what it is and does. In this class, you have already done several tasks within excel and have utilized it to help answer other problems. The key is to create a table and LABEL things as you type them in. Essentially what we will be doing is creating a calculator, but it is important to label what you are entering and what you are calculating. The screenshots below will walk you through the process:
In the screenshot above, I have entered all of the information into an excel table, also notice that I labeled what type of an interest rate I am working with, as well as labeled what unit time was in. This is an important part that oftentimes people forget. Remember that the unit of time must be the same for the rate as it is for the time period. That is why it is good to get into the habit of checking through labeling. The only part left is to enter a formula to do the calculating for us:
Once you select the “Financial” drop down menu scroll down until you find the FV formula and select it:
I prefer to utilize cell referencing when creating these calculators. This means rather than typing in the information that I want use as the input data, I will link the input to existing cells:
For the rate instead of entering 8%, I referenced cell B3 which contains 8%. For the Nper instead of entering 3 years, I referenced cell B4 which contains 3. We will use this same formula for calculating annuities, but we are not there yet, so as a result we leave the Pmt section empty and ignore it. For Pv it is important to understand a simple concept here, I actually entered cell -B2. That is right I actually entered the negative symbol first, then the cell. The reason for this is another learning moment. In order to make money by investing it or loaning it to someone else, I must give the money away for a time. It is a necessary part of investing. You must allow someone else to use your money in exchange for a “promise” to repay. You do NOT have to enter the Pv as a negative, however, notice that in the screenshot above the answer is given in the “Function Arguments” box, I have it circled in red = 1259.712. Notice what happens when I do NOT enter the Pv as a negative:
The answer is returned in the negative. Excel is very literal in how it calculates. In this case it assumes that you received $1000, and will now have to pay someone $1259.71. It is not a big deal, but it can cause issues if you use the answer to calculate further problems. The end result of the FV formula:
The reason this method of calculating future value problems is generally preferred once students get the hang of using excel is that it can easily be manipulated. Say you want to know how much the FV would be worth if you invested the $1000 today for 10 years at 5%. All you need to do is adjust the Rate and the NPER:
For a quick practice calculate the following Future Value Problems:
- PV = $2500, I = 5.05%, n = 10 years
- PV = $20,550, I = 4.45%, n = 15 years
- PV = $40,000, I = 3.23%, n = 4 years
- PV = $10,223, I = 2.5%, n = 12 years
Future Value of an Annuity
Future Value of an Annuity
Annuities are a popular method of investing. The essential idea is that you pay a lump sum amount of money today, and receive a series of payments over time, great for retirement lifestyle. Or you make a stream of payments (say $500 per month) over time and receive a lump-sum payment in the future. The latter is an example of a future value of an annuity problem. The idea is that we will save a certain amount every time period (can be weeks, months or years), that investment will earn interest, as well as continue to grow as we continue to add additional dollars to the investment each time period. The streams of investment are known as payments (PMT) which we skipped over in the Future Value section when learning how to work these problems on excel. Below is a visual representation of a FV of an annuity:
When we work on FV of annuity problems, we know what the PMT is. What we are wanting to know is, at a given interest rate and a set series of payments, how much will the investment be worth at a certain time in the future? Imagine we have decided to start saving $1000 annually at 8% APR. We want to know what it will be worth at the end of 3 years. Again, there are multiple methods for solving these problems, and we will go through each one individually. One last note before we go and start calculating the answers to these problems, we skipped over another item on the excel problems in the previous unit – type. The type is looking at when the compounding takes place – the beginning of the time period or the end. Typically, the compounding takes place at the end of the time period. Which makes sense. If you are the one paying an interest for the use of someone else’s money, you wouldn’t want to pay them until the end of the period. Most of the problems will be assumed to have the compounding take place at the end, which is why on excel, we left that section blank (see screenshot below):
Notice on the function arguments when you are in the “Type” section, the definition shows up below:
Type is a value representing the timing of payment; payment at the beginning of the period = 1; payment at the end of the period = 0 or omitted.
Since most often, the payment occurs at the end of the period, the default is if you leave that section blank it assumes that the payment and thus the compounding occurs at the end. Bottom-line, assume that the payment occurs at the end of the period, unless you are specifically told that it occurs at the beginning.
Table
Just like a Future Value problem, we can calculate the answer by using a table format going on a year by year basis. It is still time consuming and tedious, but it is also a good way to learn what is actually going on during the compounding. See the table below:
| Year | Value at beginning of the year | Interest rate (%) | Interest earned ($) | Payment ($) | Value at end of year ($) |
| 1 | $0 | 8 | 0.00 | 1000.00 | 1000.00 |
| 2 | $1000 | 8 | 80.00 | 1000.00 | 2080.00 |
| 3 | $2080 | 8 | 166.40 | 1000.00 | 3246.40 |
The table illustrates how the money grows as we add it to the investment. There isn’t any interest earned in the first time period since the payment is not made until the end of year.
Formula:
The mathematical formula for computing the future value of an investment is as follows:
FV = PMT x
where FV = future value, PMT = payment, i = interest rate and n = time periods or nper
To calculate our problem from earlier, e plug in our information into the formula to get:
FV = $1000 x => FV = $1000 x => FV = $1000 x => FV = $1000 x 3.2464
FV = $3246.40
Let’s be honest. That is not a fun equation to have to work through every time. So I highly recommend learning how to use excel in the following section!
Excel:
The formula for the FV of an annuity is the same as the FV. Remember how we ignored the PMT section in the formula before? That is what we will be using now:
Once again, we entered the PMT as a negative since we must give up $1000 each year, in order to get back the $3246.40 at the end of year 3. If we would have entered the PMT as a positive we would get this for the answer:
This is NOT the end of the world, but it does cause some issues when using the answers in further problems. That is why I always recommend entering all of the input data so that it shows up as a positive answer.
For a quick practice calculate the following Future Value Problems:
- PMT = $15,000, I = 5.65%, n = 10 years
- PMT = $500, I = 4.45%, n = 25 years
- PMT = $1000, I = 3.23%, n = 4 years
- PMT = $1000, I = 2.5%, n = 15 years
It is possible to make a lump-sum payment (a PV) today and then continue to contribute with periodic installments or payments. In that case, you would enter both a PV and a PMT, all other fields remain the same:
Assume you save $25,000 today from an inheritance, and then continue to invest $2500 at the end of each year for the next 30 years at 5% APR. What will be the FV?
What if the payment was made at the beginning of the period?
Present Value
Present Value Calculations
Present value refers to the value today of a sum of money to be received or paid in the future. Present values are found by discounting. And the interest rate is referred to as a discount rate. The image below represents the visual of what it means to go from a FV to an PV. The goal in these problems is to figure out what a certain amount of money that you know you will have, or want to have in the future is worth right now, given the current discount rate. In the section that follows you will learn the math, as well as tools that can be used to do these calculations.
When we work on PV problems, we know what the future value is. This is sometimes a little bit difficult for people to grasp. How do we NOT know what something is worth today? Think of this in two different ways. We are either going to receive a set amount of money at some time in the future and we want to know what it is worth today, or we want to have a certain amount of money saved up by a date later in the future and we want to know how much we need to save today in order to get that amount saved up. Tables are difficult and cumbersome to use when discounting and so are formulas, there is essentially one way to calculate PV problems, excel (that isn’t necessarily true, but now that you have a good grasp on how to use excel, it is pointless to work through the other methods!).
Formula:
We are NOT going to work through the formula, but I did want to at the very least show it to you:
Excel
As in the previous units, labeling is very important. Assume we are trying to find out what $8000 to be paid out at the end of 3 years is worth today given an 11% discount rate?
For this problem, we will use the PV formula:
Just as in the FV problems, we leave the PMT field blank, and since you are NOT told anything about when the discounting will occur (beginning or the end of the period) the default is that it will occur at the end of the time period, thus we leave the Type field blank as well (or enter 0). Again to make things simple and not end up with a negative answer, we also enter the Fv field as a negative.
For a quick practice calculate the following Present Value Problems:
- FV = $2500, I = 5.05%, n = 10 years
- FV = $20,550, I = 4.45%, n = 15 years
- FV = $40,000, I = 3.23%, n = 4 years
- FV = $10,223, I = 2.5%, n = 12 years
Present Value of an Annuity
Present Value of an Annuity
When we look at the present value of an annuity, we are now looking at how much money we will need to invest today, in order to guarantee a certain level of payments in the future. This is a classic question for a retiree, or someone nearing retirement. In retirement, you do not have a job, which means no stream of income (except for social security). So many retirees will take their savings and retirement accounts, take the lump sum and place them into an annuity that makes a periodic payment for a certain amount of time. How much money is placed into the annuity is dependent on how much they have access to, as well as what the periodic payment needs to be. In this section, we will again jump right to the use of excel for calculating these problems for simplicity. We will also add in another issue that we often run into, when the rate and the time period do not match (i.e. the rate is an APR, but the pmts are made monthly). The image below is an illustration of what the PV of an annuity looks like:
Formula:
We are NOT going to work through the formula, but I did want to at the very least show it to you:
PV = PMT x
Excel
As in the previous units, labeling is very important. Assume we are trying to find out what annual payments of $1000 to be paid out at the end of each year, for the next 3 years is worth today given an 8% discount rate?
In other words, if I want to receive payments of $1000 at the end of each year, for the next three years, given a discount rate of 8%, I would need to place $2577.10 into an annuity today. Again, there isn’t much difference in the present value problems and the present value of an annuity, with the exception that we are entering the Pmt and not the Fv. Also, again notice that we enter the Pmt in as a negative so that answer is returned to us as a positive. Technically though, in order to receive the $1000 payments at the end of each year for the next three years, you would need to give up $2577.10 today.
Rate and nper not in the same unit
I mentioned earlier, that sometimes the rate and the time period aren’t in the same units. I also mentioned at the beginning of the Time Value of Money unit, that the two must be in the same unit of time in order to work the calculations. This really isn’t a problem; we just need to make an adjustment at some point. Take the following example:
You are planning on retiring and would like to place some of your life savings into an annuity that makes monthly payments over the next 20 years. There are 12 months in a year, so you would be looking at 20 years * 12 months/year = 240 months or 240 payments. Assuming an APR of 5%, how much money would you need to invest in the annuity today in order to receive a monthly payment of $2000 at the end of each month?
The problem here is that the pmt and the nper are in monthly units, but the rate is in annual units. We must do a calculation to the rate. Since there will be a total of 240 pmts, the nper must be in months, and since the nper must be in months, so to does the rate. To adjust an APR to a MPR (monthly percentage rate) you simply take the APR and divide it by 12 - %5/12 = 0.417% MPR. This is a lot easier to do in excel, than with a computer:
This problem is incorrect, because the rate and the nper are in different units of time. We need to convert the rate into an MPR, to do this simply enter the following formula in the rate cell:
Notice that I started with an “=” in cell C3, that tells excel that you want it to do a mathematical equation. I also adjust the label to an MPR so that I know that I have made the adjustment. The final answer looks like:
Again, since the payment is made at the end of the time period, we leave the Type field blank. On occasion you will be told that the payment is made at the beginning of the time period, at that point you will need to enter a “1” into the Type field.
For a quick practice calculate the following Present Value Problems:
- PMT = $15,000, I = 5.65%, n = 120 months
- PMT = $500, I = 4.45%, n = 25 years
- PMT = $1000, I = 3.23%, n = 48 months
- PMT = $1000, I = 0.5% MPR, n = 15 years
In problem #1 since the Nper is in months, that means that the payment will be made every month and therefore the rate needs to be converted to an MPR (divide the APR by 12). The same occurs in #3 but notice I did NOT change my label to MPR. That is why it is so important to label things and make sure you adjust your labels! In problem #4, since the payment is made annually, the rate needs to be adjusted from an MPR to an APR. In order to accomplish this simply multiply the MPR of 0.50% * 12 months/year = 6.00% APR.
One last note on adjusting rates. When you do it, just use excel to make the adjustment. Here is why:
If you use your calculator to adjust 5% APR to an MPR you will get this:
When you type your answer into the excel sheet you will most likely just type in 0.42%, which in fairness is what would show up:
The problem is that when you dig deeper in excel and expand the decimal:
We are working with this as the rate: And Notice the difference in the answer:
Granted the difference is small, but it is a difference, nonetheless. I cannot urge you enough to stop using your calculator or your phone to do these calculations and start using excel for all of them!
PMT Formula
The PMT Function
This last section covers the idea of the payment or PMT. It is handy when you have a sum of money that you are looking at investing into some type of an annuity to use the PMT formula in excel so that you will know how much your payment will be. Or when you know how much money you want to have in the future, how much your periodic payment will need to be.
The PMT formula can also be used to calculate loan payments for vehicles, houses, etc. when loans are taken out. We will be using that function more in the upcoming units – simple interest loans and amortized loans.
The classic example of this is if you were in a car accident caused by the other driver. Their insurance company might offer to settle with you over the claim. Oftentimes they will offer you a structured settlement. Which is a fancy way of saying that they are going to make periodic payments to you, each time period for a set amount of time – a payment! How much will that payment be? Again, that depends on the present or future value, the rate, the time period and the type (payment made at the ending or the beginning of the time period).
For example, you are in a car accident. The insurance company for the other driver offers you a settlement of $65,000 received today, or you can receive the payment in a structured settlement (an annuity). If they will pay out over the next five years making monthly payments at a 4.5% APR interest rate, making the payments at the end of the period, how much will the monthly payment be for?
Excel is the simplest method to solve this problem. You will again enter the following information:
Present value = $65,000, nper = 60 months, rate = 4.5%/12, type = 0 or omit
Select the PMT formula from the Formula ribbon > Financial > PMT
In other words, if I took the settlement offer from the insurance company in the form of a structured settlement, rather than receiving $65,000 today, I would instead receive $1211.80 at the end of each month for the next 5 years. Again a couple key points; we adjusted the APR of 4.5% to an MPR of 0.38% and we did the calculation in excel by entering =4.5%/12 directly into cell B3 (this eliminates any rounding errors, and we entered the time period as 5 years * 12 months/year = 60 months. One thing to note is that since the option was to take the $65,000 today, the $65,000 is a PV. There is also a place to enter the FV in the PMT function which we will look at next.
Assume you would like to have $1,000,000 saved up in a retirement account by the time you turn 65. You are currently 20 years old and you want to know how much you would need to save each month in order to have $1,000,000 by the time you turn 65 given a compounding rate of 3% APR. The information for excel looks like this:
We must modify the rate like this: We must modify the nper like this:
First, we have to determine how many years we will be saving for, then multiply those years by 12 to get the correct number of months.
Loan Payment Calculations
The PMT function can also be used to calculate the payment amount on an Equal Total Payments (ETP) loan (we will learn more about those in the Amortized Loans Unit). Imagine that you borrow $45,000 from the bank to purchase a new truck. The bank quotes you a 5% APR, making annual payments at the end of the year for 5 years. What will be your annual payment?
Enter the following information into excel:
PV = $45,000, rate = 5.00% APR, nper = 5 years:
In other words, you will make 5 annual payments at the end of each year for $10,393.87. One huge advantage to knowing how to do these calculations is the ease of changing the terms of the loan. Say another bank offer the same loan but at a 4% APR, you can quickly check the result by simply changing the rate:
A 1% reduction in the rate lowers the annual payment by $285.65. Using excel allows for simplicity in recalculating loan payments given different loan terms.
For a quick practice calculate the following PMT Problems:
- PV = $85,000, I = 5.65% APR, n = 5 years – structured settlement
- FV = $500,000, I = 4.45%, n = 25 years - savings calculator
- PV = $40,000, I = 3.23% APR, n = 48 months – car loan
- FV = $10,000, I = 0.4% MPR, n = 60 months - savings calculator