Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
BasicsCalculatorsFundamental analysis

Time Value Of Money (Excel Formula – Calculator)

Written by admin
Fri Sep 21 2018
Time Value of Money Formula Excel
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Time Value of Money Formula Excel
 

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.

Time Value of Money Formula Excel
Time Value of Money Formula Excel

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.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Oppenheimer Holdings Inc. Class A (DE) logo

Oppenheimer Holdings Inc. Class A (DE)

42.74
 
USD
 
1.08
 
(2.59%)
85
Rank
Optionable: Yes
Market Cap: 451 M
Industry: Capital Markets
52 week range    
28.41   
   50.14
Midland States Bancorp Inc. logo

Midland States Bancorp Inc.

27.57
 
USD
 
0.66
 
(2.45%)
83
Rank
Optionable: No
Market Cap: 589 M
Industry: Banks - Regional - US
52 week range    
23.48   
   30.60
Kroger Company (The) logo

Kroger Company (The)

48.41
 
USD
 
-0.78
 
(-1.59%)
83
Rank
Optionable: Yes
Market Cap: 35,325 M
Industry: Grocery Stores
52 week range    
41.82   
   62.78
Ingles Markets Incorporated Class A Common Stock logo

Ingles Markets Incorporated Class A Common Stock

100.76
 
USD
 
-0.34
 
(-0.34%)
83
Rank
Optionable: Yes
Market Cap: 1,910 M
Industry: Grocery Stores
52 week range    
74.63   
   102.88
Amdocs Limited logo

Amdocs Limited

89.99
 
USD
 
1.13
 
(1.27%)
82
Rank
Optionable: Yes
Market Cap: 10,525 M
Industry: Software - Infrastructure
52 week range    
69.79   
   90.77
Tecnoglass Inc. logo

Tecnoglass Inc.

30.37
 
USD
 
1.37
 
(4.72%)
81
Rank
Optionable: Yes
Market Cap: 1,349 M
Industry: Building Materials
52 week range    
16.05   
   34.08
Interpublic Group of Companies Inc. (The) logo

Interpublic Group of Companies Inc. (The)

34.46
 
USD
 
0.10
 
(0.29%)
81
Rank
Optionable: Yes
Market Cap: 13,330 M
Industry: Advertising Agencies
52 week range    
25.14   
   39.98
Guess? Inc. logo

Guess? Inc.

20.71
 
USD
 
-0.05
 
(-0.24%)
81
Rank
Optionable: Yes
Market Cap: 1,107 M
Industry: Apparel Stores
52 week range    
14.27   
   25.27
Bristol-Myers Squibb Company logo

Bristol-Myers Squibb Company

80.88
 
USD
 
0.60
 
(0.75%)
81
Rank
Optionable: Yes
Market Cap: 168,030 M
Industry: Drug Manufacturers - Major
52 week range    
55.76   
   81.44
Best Buy Co. Inc. logo

Best Buy Co. Inc.

86.47
 
USD
 
1.17
 
(1.37%)
81
Rank
Optionable: Yes
Market Cap: 18,947 M
Industry: Specialty Retail
52 week range    
60.79   
   112.96

More Features