Anonymous A. answered • 03/05/24
High school finance teacher with 5+ years of experience
Hi, Henry,
These types of time value of money (TVM) problems are all exercises for your Excel or Google Sheets skills. First, remember that the basic TVM functions in Excel/Sheets are PV (present value), FV (future value), RATE (interest rates/yield), NPER (number of periods), and PMT (payments). Essentially, use the function of the information you want to find.
So, in this case, we are looking for a regular deposit, which is a payment, so we'll use the PMT function.
In Excel or Sheets, you enter a function with the equal sign first and then the function name. Excel or Sheets will then pop up a help box to guide you through entering the data, but here's what we need:
=PMT (interest rate per compounding period, number of compounding periods, present value, future value, beginning or end of period)
The key for monthly or any non-annual compounding is to be sure that the interest rate and number of periods match in terms of frequency. By that, I mean each needs to be expressed in the same terms, or, in this case, months.
So here are our inputs for this problem:
Interest rate = 8% / 12 [Notice I'm dividing by 12 here because we have an annual rate, but we're compounding every month, so 12 times per year]
Number of periods = 37 * 12 [Notice I'm multiplying by 12 here because we are compounding every month, so the total number of periods is years * 12 months per year]
Present value = 0 [you do not have anything invested yet]
Future value = 4,000,000 [the amount for which you are aiming]
Beginning or end of period = end [0]
So here is what our function in Excel/Sheets will look like with those inputs:
=PMT(.08/12 , 37*12 , 0 , 4000000 , 0)
Note that the last input, 0, signals the payments occur at the end of each period (1 would signal the beginning of each period). The great part about Excel/Sheets is that you can let the program do the calculating for you even for the inputs, which is why I entered ".08/12" and "37*12." No need to calculate those yourself, either.
Once you have those data input, simply hit enter and get your answer: $1,472.54. Note the answer will be negative because you are "paying" that amount each month; in other words, negative numbers show that the money is leaving your pocket and going somewhere else (even if that is into your investment account).
Now, for the second part, you just need to think about how you earned that $4 million. It came from two sources: your monthly investments of $1,472.54 over 37 years and the interest you earned on those investments over 37 years. Thus, if we know one of those two totals, we can simply subtract it from $4 million to find the other one.
Luckily, we can figure out how much you invested total over 37 years by multiplying your monthly investment by 12 (for one year) and then by 37 (for the total time period):
1472.54 * 12 * 37 = $653,806.44
So $653,806.44 of your $4 million came from your monthly investments. Now, we simply subtract that total from $4,000,000:
$4,000,000 - $653,806.44 = $3,346,193.56. So, we see that $3,346,193.56 came from interest.
I hope that explanation helped you, and you can always use the same functions to find even more TVM-related information. Please let me know if I can help with any other questions.
