Time Value of Money — Microsoft Excel
In this article, we will walk you through the core concept of value of money in accounting and finance using excel. As accounting and finance professionals, we must know how to evaluate a business.
In this section, we will learn about the types of valuation, like NPV, IRR, PP, ARR, CAPM, WACC of a business project with different scenarios.
Further on in the section, we will discuss business valuation modeling including discounted cash flow and free cash flow.
Time Value of Money
A fundamental financial principle known as the Time Value of Money states that due to the potential for future profits, an amount of money is worth more today than it will be later. It implies that a sum of money obtained today is worth more than the same sum received in the future. Investments are the only way for money to expand. An opportunity is wasted when an investment is postponed.
TVM is regarded as a key idea in inflation since it plays a significant role in determining the purchasing power. TVM is significantly impacted by inflation, which reduces the purchasing power of money and lowers its worth. The best examples of time value of money are savings accounts and federal savings bonds.
Future Value
Time value of money tell us about future value.
Formula:
Where FV is the Future Value of Money. PV is the Present Value of Money, i is the rate of interest, n is the number of compounding periods per year and t is the number of years.
Example: Let us consider that we are investing money ₹1,00,000/- in an FD for a period of 10 years and the interest rate is fixed at 8% compounded annually. The
calculation will be as shown in the figure below.
So, after investing ₹1,00,000/- for 10 years 8% compounded annually, we will get ₹2,15,892.5.
If we calculate it year wise, as shown in the above figure cell no. D19, we get the same figure as when we calculate it with the formula as shown in cell F3. Excel has an inbuilt formula for FV = FV(rate,nper,pmt,[pv],[type]).
Syntax:
=FV (rate, nper, pmt, [pv], [type])
Arguments:
- rate: The interest rate per period.
- nper: The total number of payment periods.
- pmt: The payment made each period, must be entered as a negative number.
- pv: The present value of future payments, assumed to be zero. if omitted, PV must always be entered as a negative number.
- type: When payments are due, 0 = end of period, 1 = beginning of period, the default is 0.
FV is again the same in the year wise table and formula, which is 2,15,892.5 in Cell F4.
Excel Formula =FV(B3 B4,B4*B5,0,-B2)
In the preceding formula, B3/B4 is the rate of interest that is divided by no. compounding period per year. In our example it is annually = 1. Half yearly, it is = 2 and quarterly, it is = 4.
The same is for B4*B5 is the nper, the total number of payment periods per year.
The Present Value (PV) of money that will be received in the future can be calculated using the formula.
The preceding is the formula of FV as we discussed earlier, now we can reverse this to calculate the PV of a FV.
Let us look at the example,
Let us consider that FV is ₹2,00,000.
So, we are getting ₹2,00,000 from FD for the period of 10 years at a fixed interest rate
of 8% compounded annually. So, the calculation will be as shown in the figure below.
Figures of PV by year wise table Cell No. D19 and PV by formula, as shown in the above figure, Cell No. F3 are the same, that is, ₹92,638.70. It means we have invested ₹92,638.70 to get ₹2,00,000 today as FV after 10 years 8% compounded annually.
Excel also has an inbuilt formula for PV as well.
PV(rate,nper,pmt,[pv],[type])
Syntax
=PV (rate, nper, pmt, [fv], [type])
Arguments:
- rate: The interest rate per period.
- nper: The total number of payment periods.
- pmt: The payment made each period, must be entered as a negative number.
- fv: The future value of present payments, assumed to be zero if omitted, PV must be entered as a negative number.
- type: When payments are due: 0 = end of period, 1 = beginning of period, default is 0.
PV is again the same in the year wise table and via the formula, which is 92,638.70 in Cell F4.
Excel Formula =PV(B3/B4,B4*B5,0,-B2)
In the preceding formula, B3/B4 is the rate of interest, which is divided by compounding period per year. In our example it is annually = 1 . For half yearly, it
is 2 and quarterly, it is 4.
The same is for B4*B5.
Let’s take an example of FV compounded quarterly, so n = 4, the calculation is shown
in the figure below.
As we can see, n = 4 in the above figure and the FV = 220804 in both cases.
Let’s take an example of PV compounded quarterly, so n = 4 the calculation is shown in the figure below.
As we can see, n = 4 in the above figure and the PV = 90578.08 in both cases.
Conclusion
Finally, we will take a fundamental drive of important concepts and formulas that is frequently used by accounts and finance professionals and we also understanding the value of money for any business and obviously every business wants to generate outflow cash against his products and spend that money in more advanced and futuristic projects that elevate the company to the outstanding success.
