Time Value Of Money (Excel Formula – Calculator)
Meet The Ultimate Excel Solution for Investors
- Live Streaming Prices Prices in your Excel
- All historical (intraday) data in your Excel
- Real time option greeks and analytics in your Excel
- Leading data in Excel service for Investment Managers, RIAs, Asset Managers, Financial Analysts, and Individual Investors.
- Easy to use with formulas and pre-made sheets
Time value of money is the change in value or purchasing power of money with the time.
In the period of inflation purchasing power of money is going down day by day. If we invest or deposit some money in the bank, then we receive a return or interest on such money. Such return or interest is the compensation for the loss of value of money for such length of the period. The money received today is more valuable than money received in the future. It is vice versa in the environment of deflation.
Types of Time Value of Money
1) The present value of money
Present value is the value today of an amount that is receivable in the future with the investment rate for the period of time. The investment rate is the discounting rate or the hurdle rate. We can calculate it by using the technique of discounting.
2) Future value of money
Future value is the compounded amount of money after a period of time with the interest rate. It is calculated by the technique of compounding.
Difference between simple and compound interest
Simple interest is due periodically and paid periodically. It is not accumulated with the principal amount.
Amount (A) = P + I
Simple interest (I) = (P x R x N)/100
A = Amount
P = Principal
R = rate of interest
N = No. of years
Even though, the compound interest is due periodically it’s not paid regularly there it is accumulated with the principal.
A = P(1+r/100)^n
Compound interest (I) = A – P
A = Amount
P = Principal
r = Rate of interest
n = No. of years
Compound interest is interest on (principal + interest)
Frequency
Compounded Semi-annually : r = Annual rate/2, n=no of yrs. X 2
Compounded Quarterly : r = Annual rate/4, n=no of yrs. X 4
Compounded Monthly : r = Annual rate/12, n=no of yrs. X 12
How to calculate the effective interest rate
Nominal interest = 9 %
If compounded quarterly
A=100[1+(9/4)/100]^1*4
A=100(1.0225)^4
A=109.30
Effective interest (I) = A – P
Which is = (9.30/100)*100
Therefor, the effective interest rate is 9.30%
Annuity : is the regular payment or receipt at fixed intervals.
The types of annuities
1) The regular annuity is the amount we get or pay at the end point of each period.
For example, we deposit USD 500 in the bank at end of every month.
2) The immediate annuity is money we pay or receive at the start point of each period.
For example, we pay the rent on the 1st day of every month.
When in doubt, its generally assumed to be the regular annuity.
| Type | Future value | Present value |
| Regular | P[(1+i)^n-1]/i | P{[(1+i)^n-1]/i(1+i)} |
| Immediate | P(1+i)[(1+i)^n-1]/i | P(1+i){[(1+i)^n-1]/i(1+i)} |
We derive the formulas above using the geometric progression.
To summarize, the change in the value of money with the period of time in both inflation and deflation period can be thought of as the Time value of money.
I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.