1. Time value of money calculations in Excel
Hello! In this screencast, we will review the time value of money calculations, such as future and present value.
Let’s say that as we’re creating our business plan for Pink Elephants, we also have another opportunity to invest our start-up capital of $1M into another business with a guaranteed 7% annual growth rate over the next four years. We can use the future value to determine what this would be worth.
To simplify our calculation, we’ll type out all our assumptions here: our rate is 7%, our number of periods is 4, and our present value is $1M.
Let’s use the future value function now that we have all our inputs; let’s review the syntax as we fill this in. Rate is the interest rate. nper is the total number of payment periods. PMT is an optional field used for annuities; we won’t use this in this course. PV is the present value of the investment amount. Finally, Type is an optional field that is used for annuities as well, so we won’t use it in this course either.
Now we have our answer from the future value function. Notice the future value formula produced a negative value. This is because it assumes the present value is negative because it is an outlay of cash. We can simply fix this by putting a negative in front of the formula.
Let’s use the mathematical formula for future value to see how exactly the future value function works. First, we start with our present value. Then we need to grow our money by multiplying it by one plus the interest rate to the number of compounding periods.
In our second scenario, let’s find the present value of Pink Elephant’s net income in year four at a discount rate of 10%. First, let’s set up our assumptions: our rate is 10%, and our number of periods is 4.
Our future value can be referenced from our financial model sheet. Notice that when we reference a cell on another sheet, the sheet name appears in the formula ribbon. Now we can just hit enter and it will take me back to our cell where the formula is. Since this references another sheet within the same workbook, we should color the font green to align with our formatting protocol.
Okay, let’s use the present value formula to solve this problem. Notice that the syntax for present value is almost the same as the future value. So let’s fill this in, putting our rate for rate, number of periods for nper, skipping our payment, referencing our future value, and skipping type. Once again, this created a negative value, so we’ll just put a negative sign in front of the formula.
Let’s break this function down again using the mathematical formula. First, we start with our future value. Then we need to discount our money by dividing it by one plus the interest rate to the number of compounding periods.
And there we have it. Not too hard, right? Conceptually understanding the time value of money is harder than performing the actual calculations, and with some more practice, this is a skill and concept you will master.
Now you’ve seen it, it’s your turn to give it a try!
2. Let's practice!