Last Update: December 7, 2024
Introduction
The Time Value of Money (TVM) Concept states that money tends to lose its value (purchasing power) over time.
TVM concepts are ubiquitous in our world. Financial companies as well as individuals use TVM concepts and methods to make various financial decisions. For example:
- Deciding which corporate project or projects to spend money on
- Choosing among investment options
- Choosing among loan options and conditions
- Planning for retirement
- Planning for educational costs
- Deciding on whether to buy or lease
- Deciding on whether to accept a lump sum versus a series of payments (annuities)
In this post, we’ll
- Define and describe the key Time Value of Money (TVM) formulas. (Compounding of Single Cash flow, Annuities, Perpetuities, Discounted Cash Flow Formulas)
- Derive the various forms of the annuity and perpetuity formulas (refer to the Appendices for these)
- Introduce you to financial calculator (HP 12c) and spreadsheeting tool (Microsoft Excel and Google Sheets) functions that do TVM calculations.
Linked Menu
- Introduction
- Value of Money Today vs the Future
- Interest
- Simple Interest
- Compound Interest
- Continuous Compounding, Effective Rates, and Equivalent Rates
- Using a Timeline
- Multiple Cash Flows
- Annuities and Perpetuities Defined
- Sum of a Geometric Series
- Present Value Annuities
- Future Value Annuities
- Perpetuities
- Un-even (Uneven) Cash Flows
- Discounted Cash Flow Analysis
- TVM Calculations Using a Financial Calculator
- TVM Calculations Using Microsoft Excel (or Google Sheets)
- Conclusion
- Appendix 1 – References
Financial Equation Derivations
- Appendix 2 – Sum of a Finite Geometric Series
- Appendix 3 – Sum of an Infinite Geometric Series
- Appendix 4 – Annuity Equation Conversion Factors Diagram
- Appendix 5 – Discount Factor v, Effective Rate of Discount Factor d, and some useful Limit relationships.
- Appendix 6 – Present Value Ordinary Annuity (PVOA)
- Appendix 7 – Present Value Annuity Due (PVAD)
- Appendix 8 – Present Value Geometric Growing Ordinary Annuity (PVGOAgeom)
- Appendix 9 – Present Value Geometric Growing Annuity Due (PVGADgeom)
- Appendix 10 – Present Value Arithmetic Growing Ordinary Annuity (PVGOAarith)
- Appendix 11 – Present Value Arithmetic Growing Annuity Due (PVGADarith)
- Appendix 12 – Future Value Ordinary Annuity (FVOA)
- Appendix 13 – Future Value Annuity Due (FVAD)
- Appendix 14 – Future Value Geometric Growing Ordinary Annuity (FVGOAgeom)
- Appendix 15 – Future Value Geometric Growing Annuity Due (FVGADgeom)
- Appendix 16 – Future Value Arithmetic Growing Ordinary Annuity (FVGOAarith)
- Appendix 17 – Future Value Arithmetic Growing Annuity Due (FVGADarith)
- Appendix 18 – Present Value Ordinary Perpetuity (PVOP)
- Appendix 19 – Present Value Perpetuity Due (PVPD)
- Appendix 20 – Present Value Geometric Growing Ordinary Perpetuity (PVGOPgeom)
- Appendix 21- Present Value Growing Perpetuity Due (PVGPDgeom)
- Appendix 22- Present Value Arithmetic Growing Ordinary Perpetuity (PVGOParith)
- Appendix 23- Present Value Arithmetic Growing Perpetuity Due (PVGPDarith)
- Appendix 24 – Continuous Compounding Equation Derivation
Why is Money More Valuable Today Than Tomorrow?
You’ve probably heard the saying, “A dollar in hand today is worth more than a dollar in the future.” Why is that?
The answer becomes pretty obvious using a simple (somewhat ridiculous) example:
If somebody offered you $100 today or $100 a year from now. Which offer would you take?
Opportunity Cost
Why would you wait a year if you could get the same thing today?
There is no incentive for you to wait a year, so you wouldn’t.
In a more realistic scenario maybe the choice would be $102 in the future. You now have to ask yourself, is there another investment you could do today with that 100 dollars that would give you more than $2 back in 1 year?
This alternative option benefit (investing the $100 today) is called an Opportunity Cost.
You might well find that you can make more than $2 investing in a relatively safe bond for example. So you would probably still take the $100 dollars today.
Did you consider inflation in your decision making?
Inflation
In general, prices tend to rise over time and so the purchasing power of the dollar will decline in the future.
So $100 in the future is a “no go” since it won’t have the same purchasing power as $100 today.
For a more realistic future offer, a realistic assessment would require not only an investment opportunity analysis , but also an inflation analysis.
Risk (Uncertainty)
You never know what is going to happen in the future.
Again, you need an incentive to delay a sure thing for a potential thing.
If you offer me a value greater than $100 in the future I might consider it , given a thoughtful analysis of investment alternatives, an understanding of inflation effects, and an assessment of the risk.
So alternative investments (opportunity cost), inflation, and uncertainty (risk) tend to make a dollar (or any other currency) more valuable today than it will be in the future.
Notice I’ve used the word “tends” a few times because there could be a deflationary scenario ,for example, where prices drop going forward.
Interest Rate
The future and present value of money are related to its “growth” rate.
The growth rate can generally be called the Interest Rate but it could be called other things depending on its function. For example:
- Interest Rate – A savings account rate or a bond rate
- Inflation Rate – The loss in purchasing power of a currency (general increase in prices over time).
- Rate of Return or Growth Rate – The value performance of an investment over time.
- This could be further broken down as a Real Rate (does not include inflation effects) and a Nominal Rate (the actual rate meaning inflation is included).
- Loan Rate – The interest you pay on a home or car loan or the interest you get on a bond investment.
- Loan Rates are built up from Risk Free Rates (like short term US Treasury Bills) with additional premiums added on depending on the situation.
- Premiums can account for Inflation, Borrower Default Risks, Liquidity Risks (ease of access to capital), and Maturity Risks (time related risks like interest rate changes which affect bond prices).
- Discount Rate – The hurdle rate or opportunity cost used often by corporations/businesses to compute the present value of capital projects
Interest Rates (or more generally any kind of rate e.g. investment rate of return) are related to money and time.
Rates are fundamentally related to the Present (i.e. value now; we can call it P or PV) and and Future Value (F or FV) via one of two simple relationships:
- F = P(1+nr) = Simple Interest Formula
- F = P(1+r)n = Compound Interest Formula
where n is the number of periods and r is the period rate (loan interest rate or investment growth rate).
Almost all useful Time Value of Money formulas are derived from the Compound Interest Formula.
In the next two sections we’ll derive these Simple and Compound Interest Formulas.
Simple Interest
Consider the following scenario:
You invest an amount P in a bank savings account. The bank will pay you 2% Simple Interest annually. How much money will you have after 3 years?
After 3 years, you will have P +2%P + 2%P + 2%P = P(1+(3)(2%)).
We can expressed this generally as: F = P(1+nr) = Simple Interest Formula
Table 1: Simple Interest Formula
- F = P(1+nr)
- F = Future Value,
- P = Present Value,
- n = total number of periods
- r = Interest rate per period
- note that the same period unit has to be used for n and r
- This is called the Simple Interest Formula
Some examples where simple interest is used:
- Corporate bond interest payments (coupon payments)
- Auto loans
- Certificates of deposit
- Federal student loans
- Most private student loans
- Short term loans
Compound Interest
Now, let’s use the same example we used for simple interest but with a twist.
This time, you invest an amount P, and the bank offers you 2% interest Compounded Annually for 3 years.
This means you’ll get “interest on your interest”.
After 1 year, your investment will be worth:
P + 2%P = P(1+2%).
After the 2nd year your investment will be worth:
P(1+2%) + P(1+2%) x 2% = P(1+2%)(1+2%)
And after the 3rd year your investment will be worth:
P(1+2%)(1+2%) + P(1+2%)(1+2%) x 2% = P(1+2%)(1+2%)(1+2%)
We can express this generally as:
F = P(1+r)n = Compound Interest Formula for a Single Cash Flow.
The formula can be expressed in four ways depending on the variable that needs to be computed (see the equations in the box below).
Table 2 – Single Payment Compound Interest Formulas and Factors
Where:
- F = P(1+r)n = P(1+i/c)cy
- F = Future Value, P = Present Value,
- i = yearly interest rate (sometimes called the stated rate)
- c = number of compounding periods per year
- r = interest rate per compounding period = i/c
- y = number of years
- n = total number of compounding periods = (c)(y)
- This is called the Compound Interest Formula for a Single Cash Flow
If you want to derive Equations III. and IV. above from I. , remember your logarithm and exponent rules:
- ln ab = ln a + ln b
- ln a/b = ln a – ln b
- ln ab = b ln a
- (ab)1/b = a
- ln(1) = 0
Compounding is Everywhere
Some places where compounding is at work are:
- Bank savings and money market accounts
- Stock investments
- Compound interest on credit card overdue amounts (paying interest on overdue credit card amounts is bad news since interest rates here tend to be very high).
- Certain government bonds (EE, I, Zero Coupon).
How the Lump Sum Compounding Formula Can Be Used
In the equation, F = P(1+r)n = P(1+i/c)cy , You can solve for any variable F, P, r = i/c , n= (c)(y) as long as you know the other three.
- Example: Solve for what your investment will be worth in the future.
- Example: Solve for what you need to investment now to earn a future amount.
- Example: Solve for how long it will take your investment to grow a target amount
- Example: Solve for your investment rate of return.
The Power of Compounding
The exponential component of compound growth allows your investment returns to accelerate over time.
Every investor should know that it’s important to invest early and let time work for her/him.
Check out my post on the power of compounding.
The Good and the Bad
Compounding is also a “double edged sword”.
Compounding works for you in investing but can work against you when you borrow!
An alarming example is the current US Federal Government Interest payments on the debt.
Another example that might hit closer to home is credit card debt (interest rates can get really high…they are 20% and “change” as I write this on Feb 13, 2024).
The Compounding Formula is Foundational
The compounding formula, F = P(1+r)n = P(1+i/c)cy is the Foundation of most Time Value of Money (TVM) Calculations.
This will be more evident in later sections when we discuss multiple cash flow scenarios (Annuities, Perpetuities, and irregular cash flows).
Continuous Compounding, Effective Annual Rate, and Equivalent Rate
In the compounding formula, F = P(1+i/c)cy = P(1+r)n , while the stated interest rate (i) is a certain value, the actual or effective interest might not be the same.
It depends on the frequency of compounding (c).
Consider the Compounding Equation for a Single Cash Flow, F = P(1+i/c)cy = P(1+r)n
Let
- F = Future Value
- P = Present Value
- y = number of years
- i = annual interest rate (typically the “stated rate”)
- c = compounding periods per year
- r = rate per period = i/c
- n = number of periods = (c)(y)
- Compounding Factor = (1+i/c)cy = (1+r)n
- e = Euler’s number = 2.71828…
- EAR = Effective Annual Rate
If the compounding is done more frequently than annually (i.e. semi-annually, quarterly, etc.), the actual annual interest rate (or Effective Annual Rate or EAR) will be higher.
Consider the Table 3 example below.
Table 3 – Continuous Compounding and Effective Annual Rate Example
Table 3, shows the future value of $100 (P) invested for 10 years (y) at a 7% stated annual interest rate (i) for different compoundings (c) per year.
As the compoundings increase from row 1 to 6 in the table, so does the future value (from $196.7 to $201.38).
We see that:
- As the number of compoundings per year go towards infinity, the future value equation for discrete compounding F=P(1+i/c)cy becomes the equation for Continuous Compounding F = Peiy (where e = Euler’s number = 2.71828…; which is a non-terminating irrational number).
- Euler’s number is fascinating. See Appendix 24 for more on Euler’s number and the derivation of the continuous compounding formula.
- As the yearly compounding periods increase, the actual annual interest rate (or Effective Annual Rate) gets increasingly larger than 7% (the stated annual compounding interest rate).
- The Effective Annual Rate (EAR) is simply the annual compounding factor minus 1 (the 1 converts it to a fraction or percent) or (1+i/c)c-1.
- For example, for annual compounding, the EAR is 7% while for monthly compounding the EAR is actually 7.23%. At the limit (when c is infinite), the EAR is 7.25%.
Equivalent Rates
It’s useful to know how to express an annual rate as the equivalent rate per period
So, if I have an annual rate of “a” , how can I express this as an equivalent quarterly or semi-annual or monthly rate?
We can develop a useful formula with an example.
Example: If the annual rate is a%, what will be the equivalent rate if we compounded quarterly? Assume an investment of 100.
- We want to know the equivalent quarterly rate that will produce the same future value as the annual rate.
- 100(1+a)=100(1+quarterly rate)(1+quarterly rate)(1+quarterly rate)(1+quarterly rate)
- 100(1+a) = 100(1+quarterly rate)4
- Solve for quarterly rate: quarterly rate = (1+a)1/4-1
We can generalize this formula to: (1+a)1/c-1 = period c rate.
So for example, if the annual rate is 6% and the compounding is actually quarterly, then the equivalent rate is (1.08)^(1/4)-1 = 1.94%.
Summary
Continuous Compounding Formula: F = Peiy
The Compounding Formula becomes the Continuous Compounding Formula when there are an infinite number of compounding periods, c.
Effective Annual Rate: EAR = (1+i/c)c-1
A periodic rate, c ,can be expressed as an Effective Annual Rate using this formula.
Equivalent Rate: (1+annual rate)(1/c) – 1 = period rate
An annual rate can be expressed as an equivalent periodic rate with this formula.
where,
- F = Future Value
- P = Present Value
- y = number of years
- i = annual interest rate (typically the “stated rate”)
- c = compounding periods per year
- r = rate per period = i/c
- n = number of periods = (c)(y)
- Compounding Factor = (1+i/c)cy = (1+r)n
- e = Euler’s number = 2.71828…
- EAR = Effective Annual Rate
Using a Timeline
We want to develop the compounding formula for multiple cash flows, but ,before we do, let’s discuss a very useful construct that can help you visualize the value of money across time: the timeline.
It’s a best practice to draw out a timeline when you do Time Value of Money calculations.
In simple examples, this will seem trivial but in more complex scenarios it will help you set up the problem correctly and avoid making mistakes.
Consider Schematic 1 below:
Schematic 1 – TVM Timeline
- The timeline has labeled rows I. through VI. The Cash Row (I.), shows the Present Value, P (todays value), the Future Value, F.
- note: We will address periodic payments/investments in the next section where additional cash occurs in between (and including) P and F.
- The Year row (II.) shows the year counters starting from today(0) and going to y years
- The compounding periods, c , are shown for c = 12 (monthly) , 4 (quarterly), 2 (semi-annual), and 1(yearly) periods
Lets do a couple of examples using the Compounding Formula for a Single Cash Flow: F=P(1+r)n
Single Cash Flow (Lump Sum) Future Value Calculation
Example 1: Invest $10,000 today, at 4% compounded semiannually for 5 years. What is the future value?
Remember that r = i/c and n = (c)(y) and annual, semi-annual, quarterly, monthly, and daily periods would be 1,2,4,12,365 respectively.
- P = $10,000
- i= 4%
- c = 2 ; semi annual means compounding is twice per year
- r = i/c = 2%; we must adjust the annual rate i by the number of compounding periods
- y=5 years
- n =(2)(5)=10 compounding periods
- F = 100(1+2%)10 = $12,189.9
Single Cash Flow (Lump Sum) Present Value Calculation
Example 2: The Value of your investment in 5 years, if compounded monthly at 3%, is $13,000.
What is the current or present value of this investment?
- F = $13,000
- i=3
- c = 4
- r=i/c=.75%
- y = 5
- n=(4)(5)= 20
- F=P(1+r)n , therefore, P = F/(1+r)n
- P = 13,000/(1+.75%)20= $11,195.5
In other problems you might need to solve for r or n as well and this is easily done as long as you know the other three variables.
Compounding and Discounting
The timeline concept helps us intuitively understand two commonly used TVM terms.
- When we compute the future value , we are Compounding the present value P into a future value F.
- This term is intuitive as it suggest growth of the present value into a larger future value.
- When we compute the present value from a future value we describe this as Discounting the future value.
- This term is intuitive as well and implies a reduction. The present value will be a smaller value than the future value.
All the examples so far assume there is a one time investment at time zero.
What happens when we have additional investments (or withdrawals) that occur in between the beginning and the end? Read on.
Multiple Cash Flows
In the previous section we reviewed two examples where we computed the Present and Future value of a single investment (no additional investments were assumed).
Now consider a few more examples where we make more than one investment. This is where drawing a timeline is very useful.
Multiple Cash Flows, Future Value Calculation
- These investments occur in different years. In order to express them all as a single future value, we have to adjust the values of each cash flow by the time remaining and the interest rate.
- The future value will therefore be the sum of the individual investment future values. We’ve applied the Cash Flow Additivity Principle here i.e. we can adjust each flow individually and then add them up.
- F = X4 + X3(1+6%)1 + X2(1+6%)2 + X1(1+6%)3
- Note!!! Due to the additivity principle, each term stands by itself.
- That means that the cash flows could be the same , different, periodic , or not (irregular or un-even). It doesn’t matter.
- You simply take each cash flow and adjust them accordingly to express all of them as a singular future value cash flow.
Schematic EG3.1 – TVM Timeline with Multiple Cash Flow Compounding Example
Multiple Cash Flows, Present Value Calculation
- The present value will be the sum of the individual investment present values.
- This time we’ll use the discounting form of the Compounding Formula.
- P = X1 + X2/(1+6%)1 + X3/(1+6%)2 + X4/(1+6%)3
- Note!!! Due to the additivity principle, each term stands by itself.
- That means that the cash flows could be the same , different, periodic , or not (irregular or un-even). It doesn’t matter.
- You simply take each cash flow and adjust them accordingly to express all of them as a singular present value cash flow.
Schematic EG4.1 – TVM Timeline with Multiple Cash Flow Discounting Example
Direction of Cash Flows
Any cash flow should be defined as an inflow (being received) or an outflow (an expense, payment, or an investment).
If you do financial calculations in a spreadsheet like excel or on a financial calculator, you will have to input cash outflows as negative (-) values and cash inflows as positive values.
For single (lump sum) cash flows, PV and FV will have opposite signs.
For example, if you invest $10,000 today, what will the future value be if it compounds annually at 7% for 10 years. The answer will be $-19,671.51 if you input the PV as +$10,000 (if you had input the PV as negative, the FV output would be positive.).
This becomes more important when you do multiple cash flow calculations.
Now you have three variables that assume either a positive or negative value (the present value, the future value, and the periodic cash flow). The periodic cash flow must have the proper sign.
Cash Inflow (+) Examples:
- income (pension, paycheck, savings income etc.)
- borrowed money like a home mortgage or auto loan
Cash Outflow (-) Examples:
- payments (debt, educational, purchases)
- investments (outflow from you that hopefully results in return inflows)
TVM Calculations Rules (Principles)
Let’s recap…Examples 3 and 4 showed multiple cash flows.
We computed a singular future or present value of the cash flows by applying the compounding (or discounting) equation to each cash flow (using the appropriate time period and given interest rate).
The Additivity Principle allows us to (a) convert each cash flow to the appropriate time at a given interest rate and then (b) sum them all up to get the singular present or future value.
In scenarios with multiple cash flows like Examples 3 and 4 above, you must ALWAYS convert the cash flows across multiple periods back to a singular period.
You have to do this to normalize the effects of time. Make sense, right? You cannot compare 2016 dollars with 2022 dollars for example.
You need to adjust one or the other so they are expressed on the same basis.
So, two fundamental tenets of TVM calculations are:
- You have to express multiple cash values on the same basis (PV or FV) if you are going to do any comparison or evaluation
- You need to account for the direction of Cash flow. Cash outflow (think expenditures or investments) inputs into calculator or spreadsheet tools are assumed to be negative.
Example 5: Write out the Present Value equation for a cash flow scenario where in year zero an investment of C (call it Co) occurs and at the end of the next 3 years income P1, P2 and P3 (respectively) are earned. The annual interest i is compounded quarterly and the income is paid annually and at mid year.
- PV = C0 + P1(1+i/c)(cy1) + P2(1+i/c)(cy2) + P3(1+i/c)(cy3)
- Income paid annually and at mid mid year means P1 occurs at year .5 , P2 occurs at year 1.5, etc.
- Note that if cash flow timing was end of year, then y1 = 1, y2 = 2, etc.
- c = 4; cy1 = (4)(.5)=2, cy2 = (4)(1.5)=6, cy3 = (4)(2.5)=10
- PV = -C0 + P1(1+i/4)2 + P2(1+i/4)6 + P3(1+i/4)10
- Note that Co is entered as a negative value because its a cash outflow
- Note that the P expressions are expressed positively because they are cash inflows.
We will revisit the example 4 and 5 equations in later sections when we discuss DCF (discounted cash flow), NPV (net present value) and un-even cash flows, but we want to talk about Annuities first.
Annuities and Perpetuities Defined
So far we have only considered the compounding or discounting of single investments at time P or F respectively.
Annuities and Perpetuities involve multiple cash flows that compound over time.
Formulas for Annuities and Perpetuities are based on the compounding and discounting forms of the Compound Interest Formula P = (1+r)n. (see how in the derivations located in the Appendices 3- 14.).
Annuities are constant (or constantly growing) and even (equal interval), periodic cash flows that occur (typically) weekly, monthly, quarterly, or yearly .
They last for a finite period of time. These are employed in all sorts of financial calculations (e.g. investments, retirement planning, mortgages and other loans, education cost planning, etc..).
Perpetuities are Annuities with infinite cash flows.
These don’t sound very useful but are often used by corporations and financial firms to estimate end of project ( liquidation or terminal or going concern) values.
The perpetuity concept can be also applied to rents on property investments or dividends on stocks to value those assets.
Annuities/Perpetuity Types Defined on a Timeline
Consider Schematic 2 below where we have periodic cash investments over a three year period.
Some are constant over time while others grow geometrically at a yearly growth rate g (or grow arithmetically at a constant G) .
Schematic 2 – Annuities Defined on a 3 Year Timeline (note: For infinite periods, the exact definitions apply to Perpetuities as well)
An Ordinary Annuity (or Perpetuity for infinite periods) has its first cash flow occur at the end of the period.
- e.g. In Schematic 2 row I., periodic cash amount X starts at end of year 1 and is added with the same timing in subsequent years.
An Annuity (or Perpetuity for infinite periods) Due has its first cash flow occur at the beginning of the period.
- e.g. In Schematic 2 row II., periodic cash amount X starts at the beginning of year 1 (at year =0) and is added with the same timing in subsequent years.
Growing Annuities/Perpetuities
A Geometrically Growing Ordinary Annuity (or Perpetuity for infinite periods) has cash flows that evenly grow over time at a fixed growth rate starting at the end of the period.
- e.g. In Schematic 2 row III., X starts at the end of year 1, grows to X(1+g) at the end of year 2 etc. (g is a fixed growth rate percentage)
A Geometrically Growing Annuity (or Perpetuity for infinite periods) Due has cash flows that evenly grow at a fixed growth rate beginning at the start of each period.
- e.g. In Schematic 2, row IV., X starts in year 0, then grows to X(1+g) at the beginning of year 1, etc.
Arithmetically Growing Annuities/Perpetuities have similar definitions (for Ordinary Annuity and Annuity Due) but the growth in this case is linear by a constant G (X, X+G, X+2G etc.)
Refer to the Appendices for detailed annuity equation derivations.
In the next several sections I list and define the various forms of Present Value and Future Value Annuity equations (as well as several Perpetuity equations).
I count 24 equations (not including the 4 Geometric Series equalities) that I list and discuss (with links to detailed derivations in the Appendices).
You might want to skip these sections and go right to the sections on un-even cash flows, Discounted Cash Flow, and TVM calculation tools.
Use the links below to jump to those sections.
You can access the links from the main menu as well.
Sum of a Geometric Series
The formulas for the sum of a finite or infinite Geometric series (formulas II. in the box below) are used in the derivation of annuity formulas. Refer to Appendix 2 and Appendix 3 for their derivation.
Table_4 – Finite Geometric Series Formula
Present Value Annuity Formulas
Table 5 – Present Value Annuity Formulas
Variable Definitions
- P = Present Value
- X = Periodic Payment
- i = annual rate (expressed as fraction)
- c = compounding periods per year
- r = rate per period = i/c (expressed as fraction)
- Sn = sum of a finite geometric series
- a = geometric series start term
- b = geometric series common ratio
- y = number of years
- n = number of compounding periods =(c)(y)
- g = geometric growth rate
- G = arithmetic growth constant
Present Value Ordinary Annuity (PVOA)
An Ordinary Annuity has End of Period payments. Ordinary Annuity Examples:
- Most loans like educational loans, auto loans, and home mortgages.
- Regular or recurring savings activities like with company 401k retirement accounts.
Example Use of PV Ordinary Annuity:
- How much to deposit today in order to receive a fixed amount at the end of each year (for a certain number of years) from an investment account.
The timeline below is a simple visual example of the general formula that follows (where P = P1 + P2 + P3).
P = X/r(1-1/(1+r)n)
Refer to Appendix 6 for its derivation.
Present Value Annuity Due (PVAD)
An Annuity Due has Beginning of Period payments. Annuity Due Examples that are typically paid at beginning of period:
- Retirement income
- Tuition payments
- Rental payments
Example Use of PV Annuity Due:
- How much to deposit today in order to receive a fixed amount at the start of each year (for a certain number of years) from an investment account.
The timeline below is a simple visual example of the general formula that follows (where P = P1 + P2 + P3).
P = X/r(1-1/(1+r)n)(1+r)
Note that the value of an Annuity Due is always going to be larger than the value of an Ordinary Annuity.
Refer to Appendix 7 for its derivation.
Present Value Geometrically Growing Ordinary Annuity (PVGOAgeom)
The timeline below is a simple visual example of the general formula that follows (where P = P1 + P2 + P3).
P = (X/(r-g))(1-(1+g)n/(1+r)n) ; g<r
Refer to Appendix 8 for its derivation.
Present Value Geometrically Growing Annuity Due (PVGADgeom)
The timeline below is a simple visual example of the general formula that follows (where P = P1 + P2 + P3).
P = (X/(r-g))(1-(1+g)n/(1+r)n)(1+r) ; g<r
Note that the value of an Annuity Due is always going to be larger than the value of an Ordinary Annuity.
Refer to Appendix 9 for its derivation.
Present Value Arithmetically Growing Ordinary Annuity (PVGOAarith)
A PVGOAarith equation can be developed using a timeline like the one shown below.
Refer to Appendix 10 for the equation and its derivation.
Present Value Arithmetically Growing Annuity Due (PVGADarith)
A PVGADarith equation can be developed using a timeline like the one shown below.
Refer to Appendix 11 for the equation and its derivation.
Future Value Annuity Formulas
Table 6 – Future Value Annuity Formulas
Variable Definitions
- F = Future Value
- X = Periodic Payment
- i = annual rate (expressed as fraction)
- c = compounding periods per year
- r = rate per period = i/c (expressed as fraction)
- Sn = sum of a finite geometric series
- a = geometric series start term
- b = geometric series common ratio
- y = number of years
- n = number of compounding periods =(c)(y)
- g = growth rate
- G = Arithmetic growth constant
Future Value Ordinary Annuity (FVOA)
An Ordinary Annuity has End of Period payments. Ordinary Annuity Examples:
- Most loans like educational loans, auto loans, and home mortgages.
- Regular or recurring savings activities like with company 401k retirement accounts.
Example Use of FV Ordinary Annuity:
- Compute the future value of periodic constant end of year contributions into an investment account.
The timeline below is a simple visual example of the general formula that follows (Where F = F1 + F2 + F3).
F = X((1+r)n – 1)/r
Refer to Appendix 12 for its derivation.
Future Value Annuity Due (FVAD)
An Annuity Due has Beginning of Period payments. Annuity Due Examples that are typically paid at beginning of period:
- Retirement income
- Tuition payments
- Rental payments
Example Use of FV Annuity Due:
- Compute the future value of periodic constant start of year contributions into an investment account.
The timeline below is a simple visual example of the general formula that follows (Where F = F1 + F2 + F3).
F = X((1+r)n – 1)(1+r)/r
Note that the value of an Annuity Due is always going to be larger than the value of an Ordinary Annuity.
Refer to Appendix 13 for its derivation.
Future Value Geometric Growing Ordinary Annuity (FVGOAgeom)
The timeline below is a simple visual example of the general formula that follows (Where F = F1 + F2 + F3).
F = (X/(r-g))((1+r)n-(1+g)n) ; g<r
Refer to Appendix 14 for its derivation.
Future Value Geometric Growing Annuity Due
(FVGADgeom)
The timeline below is a simple visual example of the general formula that follows (Where F = F1 + F2 + F3).
F = (X/(r-g))((1+r)n-(1+g)n)(1+r) ; g<r
Note that the value of an Annuity Due is always going to be larger than the value of an Ordinary Annuity.
Refer to Appendix 15 for its derivation.
Future Value Arithmetically Growing Ordinary Annuity (FVGOAarith)
A FVGOAarith equation can be developed using a timeline like the one shown below.
Refer to Appendix 16 for the equation and its derivation.
Future Value Arithmetically Growing Annuity Due (FVGADarith)
A FVGADarith equation can be developed using a timeline like the one shown below.
Refer to Appendix 17 for the equation and its derivation.
Perpetuity Formulas
Table 7 – Perpetuity Formulas
Variable Definitions
- F = Future Value
- X = Periodic Payment
- i = annual rate (expressed as fraction)
- c = compounding periods per year
- r = rate per period = i/c (expressed as fraction)
- y = number of years
- g = growth rate
- G = arithmetic growth constant
Present Value Ordinary Perpetuity (PVOP)
The timeline below is a simple visual example of the general formula that follows (Where P = P1 + +2 + P3).
P = X/r
Refer to Appendix 18 for its derivation.
Present Value Perpetuity Due (PVPD)
The timeline below is a simple visual example of the general formula that follows (Where P = P1 + +2 + P3).
P = (1+r)X/r
Note that the Perpetuity Due will always be larger than the Ordinary Perpetuity.
Refer to Appendix 19 for its derivation.
Present Value Geometric Growing Ordinary Perpetuity
(PVGOPgeom)
The timeline below is a simple visual example of the general formula that follows (Where P = P1 + +2 + P3).
P = X/(r-g) ; g<r
Refer to Appendix 20 for its derivation.
Present Value Geometrically Growing Perpetuity Due (PVGPDgeom)
The timeline below is a simple visual example of the general formula that follows (Where P = P1 + +2 + P3).
P = (1+r)X/(r-g) ; g<r
Note that the Perpetuity Due will always be larger than the Ordinary Perpetuity.
Refer to Appendix 21 for its derivation.
Present Value Arithmetically Growing Ordinary Perpetuity (PVGOParith)
A PVGOParith equation can be developed using a timeline like the one shown below.
Refer to Appendix 22 for the equation and its derivation.
Present Value Arithmetically Growing Perpetuity Due (PVGPDarith)
A PVGPDarith equation can be developed using a timeline like the one shown below.
Refer to Appendix 23 for the equation and its derivation.
Uneven Cash Flows
Spreadsheeting tools like Excel or Google Sheets can handle both.
We just won’t have a neat formula to use and we’ll have to “long-hand” the calculation.
The HP 12c calculator, on the other hand, can handle un-equal cash flows, but the time intervals between are assumed to be constant (equal intervals).
General Set up for a Cash Flow Problem
- Draw a timeline
- Establish the magnitude, timing, and sign of all cash flows. Make sure cash outflows are negatively signed (-)
- Determine the compounding periods (c) per year.
- You now can define the compounding factor: (1+i/c)cy where y is the number of years.
- Convert the cash flows to a constant basis:
- Bring back all cash flows to the present (discount) and sum them up to produce the total Present Value of the cash flows or
- Compound all cash flows to a future time and add them all up to produce the total Future Value.
Compute the Correct Time Distance
When cash flows are uneven, make sure each cash flow is discounted or compounded using the correct number of years (y).
I like to call this the “time distance” to the Present or Future period.
That is, for un-even cash flows, you have to make sure the exponent of the compounding factor (1 + i/c)cy , cy, is set up correctly for each individual cash flow.
In many practical applications the y is simply going to be a whole number or a simple fraction but it can be calculated for any day chosen. For example:
- You Want to discount some future cash flows to today Dec 16, 2022.
- Assume we have three cash flows Cf1, occurring on Dec 30, 2022, Cf2 occurring on March 5,2023 and a final cash flow Cf3 occurring on June 11, 2023.
- What is the Present Value of these cash flows assuming semiannual compounding (c=2) and a yearly interest rate of 5% (i)?
- We know that for each cash flow PV = Cf(1+i/2)2y.
- We know that i/c = 5%/2 = 2.5% and cy = 2y where y will vary for each cash flow:
- PV Cf1 = Cf1/(1+2.5%)^(2(Dec 30, 2022 – Dec 16, 2022 in days)/365)
- PV Cf2 = Cf2/(1+2.5%)^(2(March 5,2023 – Dec 16, 2022 in days)/365)
- PV Cf3 = Cf3/(1+2.5%)^(2(June 11, 2023 – Dec 16, 2022 in days)/365)
- The present value of the all the cash flow will be PV Cf = PV Cf1 + PV Cf2 + PV Cf3
So, if we set up the y (the year factor or the time distance) properly , we can discount or compound any cash flow that occurs at any time.
Discounted Cash Flow (DCF) Analysis
Discounted Cash Flow (DCF) analysis describes a widely used technique (especially among corporations) where the “net present worth” of all estimated cash flows (inflows and outflows) for an investment option is computed.
This net present worth or Net Present Value (NPV) has the term “Net” in it because there are both outflows (costs, expenses, investments) and inflows (income) and the difference of these on a present value basis is the desired data point.
By bringing (or discounting) all cash flows back to a present value, the NPVs of various investments can be compared and ranked on the same basis (the higher the better).
A simple example should help firm up this concept.
Discounted Cash Flow Example – Investment Option A
I need to choose between Option A and Option B. How do I do it? Lets define Option A first.
Investment Option A:
- Let Cf represent a cash flow.
- I could buy a bond today for CfAo dollars.
- It matures in 5 years and pays out a fixed amount at the end of each year (CfA1, CfA2, CfA3, CfA4, CfA5).
- The cash inflows are based on an interest rate, RFR = Risk Free Rate of Return.
We describe it as risk free because the bond is a safe and reliable government bond.
So, for this option, we know that the investment of CfA0 today produces a series of future cash flows CfA1 through CfA5 at a Risk Free Rate of RFR.
Discounted Cash Flow Example – Investment Option B
Continuing with the example, consider Investment Option B.
- I want to start a tennis teaching business.
- If I invest in a ball machine today, CfBo, I expect to earn estimated cash flows of CfB1, CfB2, CfB3 at the end of each year for 3 years.
- I will sell the ball machine for something less than CfBo at the end of year 3 so CfB3 will include both the cash flow from the business for that year but also the resale value of the ball machine.
I can compute the rate of return for this investment, IRR (internal rate of return).
In Option B, the IRR is simply the rate of return that makes the Present Value of the Cost equal to the Present Value of the Cash Flows:
PV CfB0 = PV CfB1 + PV CfB2 + PV CfB3
When discounting , the discounting form of the compounding formula is used , P = F/(1+r)n , where r is the rate per period (i/c) and n is the total number of compounding periods cy (and c is the number of compoundings per year and y is the number of years).
We are solving for r to get the IRR.
A Microsoft (Excel) or Google (Sheets) spreadsheet or your financial calculator can solve for this.
Discounted Cash Flow Example – IRR as a Decision Tool
Continuing with our example, I can compare the RFR of Option A to the IRR of Option B and choose the Option with the greater return.
Many companies evaluate and choose their investment options using this technique but it does have potential weaknesses:
- Rates of return are relative indicators.
- They give you no indication about the absolute value of the project.
- e.g. Doubling 2 dollars to 4 dollars is a 100% return but you would certainly choose making $300 from a $200 investment which is a 50% return.
- If the cash flows are not conventional (e.g. large cash outflows during the course of a project), there could be more than one IRR solution, which renders the value meaningless.
- There are other possible issues with IRR when comparing mutually exclusive investment options (only can choose one) but this will need a dedicated post to describe properly.
Another decision tool that we can use is the Net Present Value or NPV.
Discounted Cash Flow Example – NPV as a Decision Tool
Continuing with our example, we could set up Investment Option B as follows:
- Compute the present values of all the cash flows: Total Present Value = PV CfBo + PV CfB1 + PV CfB2 + PV CfB3 = Net Present Value= NPV.
- We call it Net Present Value because there are both positive and negative cash flows and we are “netting” them to a single value.
- Since each cash flow term is going to be expressed as Cf/(1+r)n, we have to choose an r in order to compute the NPV.
- Let’s use the Risk Free Rate RFR from Investment Option A and express NPV as:
- NPV = -(CfB0)+ CfB1/(1+RFR)1 + CfB1/(1+RFR)2 + CfB1/(1+RFR)3
The Net Present Value, NPV, expresses the net present value of all the cash inflows and outflows using a Risk Free Rate (in our example, it was a government bond rate of return).
This rate is usually named the Discount Rate.
The Discount Rate is often described as
- the Hurdle Rate or
- the Opportunity Cost.
It is the next best rate of return you expect to get with a relatively risk free investment (the bond investment).
Corporations often compute a Weighted Average Cost of Capital (or WACC) as their discount rate where the WACC is the average rate of return
demanded by investors (from lenders and from shareholders).
The NPV tells you if you have created additional value relative to what you could have gotten with no or very little risk (i.e. the returns you would have gotten at the RFR).
Therefore an investment option that produces a positive NPV should be considered a viable option over the risk free investment option.
Discounted Cash Flow Example – NPV possible Outcomes
In our example, there are three possible NPV outcomes.
- If NPV =0, then IRR = Discount Rate (*). Option A and B are going to be a wash and you’ll probably stick with Option A because its a relatively risk free sure thing.
- If NPV < 0 , then IRR < Discount Rate(*). There is a disadvantage to choosing Option B compared to Option A.
- If NPV > 0 , then IRR > Discount Rate(*). Relative to the risk free Option A, there is additional value being created. So we would choose Option B.
(*) for conventional cash flows, in which there is a single IRR solution (cash outflows timing can create multiple IRR solutions).
Please remember that even though NPV is an absolute number.
- NPV = 0 does NOT MEAN that the actual value of the project is zero.
- The NPV is measured against a base line value which would be the value generated by the Risk Free Investment (upon which the Discount Rate is based).
Discounted Cash Flow Summary: Formula and Key Variables
Discounted Cash Flow Formula and Key Variables:
- NPV = PV Cash Inflow – PV Cash Outflow
- Where PV (Present Value) for each cash flow term (Cf) is computed using: PV Cf = Cf/(1+r)n
- r is return (interest) per compounding period c (r = i/c) and n is number of compounding periods yc (with y = years).
- r represents the Discount Rate which can be interpreted as the Hurdle Rate or the Opportunity Cost of Capital.
- The Discount Rate reflects the next best alternative rate that the investor is confident can be obtained.
- For corporations the Discount rate is computed as the Weight Average Cost of Capital (WACC)
- The WACC is average rate of return demanded by investors in the company’s debt and equity.
- If the Internal Rate of Return, IRR, is substituted for the discount rate, the NPV will = 0 (for conventional cash flows). The IRR is the actual investment rate of return.
Discounted Cash Flow Summary: Decision Rules
- NPV > 0 means the investment is worth consideration as it produces more value then the next best alternative i.e. it produces cash in excess of cash required to satisfy the discount rate (the required rate).
- NPV < 0 means that the cash flows generated are not sufficient to meet the required rate of return (discount rate).
- For a conventional cash flow, in which there is a single IRR solution:
- If NPV > 0, IRR > discount rate: Accept
- If NPV = 0, IRR = discount rate: Not Favorable
- IF NPV < 0, IRR < discount rate: Not Favorable
- NPV is favored over IRR mainly because it is an absolute vs a relative measure. There are other possible issues with using the IRR as the primary decision tool.
TVM Calculations Using a Financial Calculator
Your financial calculator can do single cash flow and Annuity (equal value and equal period cash flow) discounting and compounding calculations.
It can also do NPV calculations.
Single Cash Flow and Annuity Calculations
The 5 main keys used for these kinds of calculations on your financial calculator will look something like the below (they look exactly like this on my HP 12c calculator).
- y = number of years
- c = number of compounding periods per year
- i = rate per compounding period = (yearly rate)/c ; (*)
- n = number of compounding periods per year = (c)(y) (**)
- PV = Present Value
- FV = Future Value
- PMT = constant Annuity payments; BEG or END (***)
Solving for a Particular Variable
Loan Calculations
Loans (debt repayments) are typically Ordinary Annuity type calculations.
Your financial calculator has a few additional keys to help you do these kinds of calculations.
Example: You take out a $200,000 home loan (30 year fixed; interest rate of 3%; monthly mortgage payments). What is the monthly mortgage payment and how much interest will have been paid after 5 payments?
- Key entries on the HP 12c: n = 30×12=360, PV=$200,000,i = 3/12=.25,FV=0.
- Pressing the PMT key gives – $843.21 = the monthly mortgage payment.
- Pressing “360 f Amort” gives -103,554.9 the total interest paid and
- Pressing the R↓ key gives -200,000, the principal paid.
(DCF) Discounted Cash Flow (NPV) Calculations
- We refer to uneven cash flows below.
- Don’t confuse this with uneven (or unequal cash flow intervals).
- The HP 12c always assumes equal intervals between cash flows.
- [CFo]: Time zero (current) cash flow amount (*)
- [CFj]: After time zero cash flow amounts for the jth period (**)
- [Nj]: A multiplier for equal cash flows (e.g. if you had 4 cash flows of 100, then you enter 10 with CFj, then with Nj you would indicate you want this repeated 4 times).
(**) Assumes end of period cash flow.
- an investment or a capital expense in time zero would be entered as a negative value and
- cash flows in future years would be entered as positive values.
- The required rate, the discount rate, to be entered using the [i] key previously noted.
- The NPV is calculated using the [NPV] button, which is a secondary (orange) key on the HP 12c.
- Alternatively, the Internal Rate of Return can be computed with the [IRR] button which is also a secondary (orange) key on the HP 12c.
TVM Calculations Using Microsoft Excel (or Google Sheets)
Microsoft Excel has all the useful Time Value of Money (TVM) financial functions.
I strongly recommend you learn how to use this universally used spreadsheeting tool.
The Google Sheets tool can also be used for TVM calculations.
Google Sheets is available for free and its TVM function names and constructs are essentially identical to their Excel counterparts.
Microsoft Excel Single Cash Flow and Annuity Functions
The main Excel TVM variables for single cash flows or Annuity cash flows are: NPER, RATE, PV, PMT, and FV
They are defined the same way they are on your financial calculator and almost similarly named (NPER = n on the HP 12c and RATE = i on the HP 12c).
- c = compounding periods per year
- y = number of years
- rate = yearly rate/c (enter as a decimal!)
- nper = number of compounding periods = (y)(c)
- pmt = constant, even Annuity cash flow
- fv = Future Value
- pv = Present Value
- [fv or pv ] means optional and defaults to 0 if omitted
- [type] = 0 = Default = End of Period Annuity. Or =1 = Beginning of Period Annuity.
In Excel, the functions are entered generally as “=Function Name(Input 1, Input 2, etc.)”.
- Solve for Annuity Payment: =PMT(rate,nper,pv,[fv],[type])
- Solve for Present Value: =PV(rate,nper,pmt,[fv],[type])
- Solve for Number of Periods: =NPER(rate,pmt,pv,[fv],[type])
- Solve for Future Value: =FV(rate,nper,pmt,[pv],[type])
- Solve for Periodic Rate: =RATE(nper,pmt,pv,[fv],[type],[guess]))
Note: In Google Sheets, the above functions are named and defined the same way.
As with the HP12c, PMT is only used when there are constant periodic cash flows (Annuities).
Loan Calculations
Loans (debt repayments) are typically Ordinary Annuity type calculations. Excel has a few functions to help you do these kinds of calculations.
Given the above and below defined variables,
- per: Specifies the period and must be in the range 1 to nper.
- nper: The total number of payment periods in an annuity.
- start_period: The first period in the calculation. Payment periods are numbered beginning with 1.
- end_period: The last period in the calculation.
- type: Timing of period. 0 = End of Period, 1 = Beginning of Period.
Excel functions for loan calculations are:
- Payments for a loan (constant rate, constant interest): =PMT(rate,nper,pv,[fv],[type])
- Interest payment for a given period: =IPMT(rate,per,nper,pv,[fv],[type])
- Principal for a given period: =PPMT(rate,per,nper,pv,[fv],[type])
- Cumulative interest paid on a loan between start and end periods: =CUMIPMT(rate,nper,pv,start_period,end_period,type)
Cumulative principal paid on a loan between start and end periods: =CUMPRINC(rate,nper,pv,start_period,end_period,type)
Microsoft Excel Discounted Cash Flow Functions
The Excel DCF functions are NPV() and IRR().
NPV Function Definition
Excel (and Google Sheets) uses the following general equation for NPV:
For n cash flows, NPV = Sumfor j=1 to n[Valuesj/(1+rate)j] which is entered in Excel as: “=NPV(rate,value1,[value2],…)” where:
- Rate = the Discount Rate (the required rate of return, the hurdle rate, the opportunity cost, etc.) for the compounding period.
- Value1, value2, … = Equal interval (*) cash flows that occurs at the end of each period (so first cash flow is always assumed to be a future cash flow).
- The NPV investment period begins one period before the date of the value1 cash flow (time zero) and ends with the last cash flow in the list.
- If cash flows occur at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments.(**)
- NPV cash flow values do not have to be equal. Cash inflows are entered as positive values.
- Cash flows must be listed chronologically.
- Typically in NPV calculations, at least one cash flow represents an investment or other expense and is entered as a negative value.
NPV Function Definition Notes
(*)
- The NPV function can be used for unequal cash flow values, but the investment timing (periods) must be equal (equal interval).
- For non periodic (non equal interval) investments we can use a special form of the NPV function (XNPV) that will discussed in a later section.
(**)
- When the first cash flow (typically the cost outflow) occurs at time zero, it should not be included in the NPV formula but must be added to it.
- Technically the NPV function in excel is really a PV function.
- I think this is an error that the Microsoft folks have not updated perhaps to maintain backward compatibility to earlier versions.
- The Google Sheets NPV function is set up the same way, interestingly.
- Also note that the CFo HP 12c input key is also designated for time 0 cash flow.
- The HP 12c input keys CFj assume end of period payments.
- So the HP 12c computes NPV by doing end of period PV calcs on the CFj inputs and then adds the CFo input to compute NPV.
IRR Function Definition
Recall that the IRR is the rate of return that produces an NPV of zero. Intuitively that make sense, right? Because if you invest X , you expect a certain compounded return to give you certain cash flows in the future.
So if you discounted those future cash flows to back to the present, using the same compounding return, you would end up with the investment X i.e. PV of Cash Outflow = PV of Cash Inflow (or NPV = 0).
The Excel function for IRR is ” =IRR(values, [guess])
- Values must contain at least one positive value and one negative value
- IRR values must be listed chronologically and have equal periods (similar to the NPV requirements)
Cash Flow Timing
The HP 12c calculator and the Excel NPV calculations assume End of Period payments.
The End of Period based NPV can be adjusted to a Beginning of Period NPV by multiplying by (1+r) where r is the rate i.e. NPVend of year(1+r) = NPVbeginning of year . Does this look familiar?
Recall from the Annuity sections that Ordinary Annuity(1+r) = Annuity Due.
These calculators and functions, however, are not designed to compute “non beginning-or-end-of-period” cash flows.
For example, annual cash flows that come in mid year, cannot be automatically computed with the Excel , Google, or HP 12c calculator functions.
Manual spreadsheet calculations (or calculator built using other programming languages) have to be used for these more specialized calculations.
DCF Calculations for UnEqual Period Cash Flows
What if the cash flow timing is unequal i.e. there are no constant periodic (equal interval) investments? Easy, just use the XNPV and XIRR functions.
XNPV function
Returns the net present value for a schedule of cash flows that is not necessarily periodic.
To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
- The Excel formula is “=XNPV(rate, values, dates)”
- Rate Required (the discount rate). This must be a yearly rate. This function wont compute correctly otherwise.
- Values are the series of cash flows that corresponds to a schedule of payments in dates.
- The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment.
- If the first value is a cost or payment, it must be a negative value.
- All succeeding payments are discounted based on a 365-day year. (so the rate must be entered as an annual rate).
- The series of values must contain at least one positive value and one negative value.
- The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
In this format you can exactly define the date of each cash inflow and outflow. Unlike the NPV function, you don’t have to exclude (but add) any time zero cash flow.
The first date entered establishes the PV baseline (time zero) for all the other cash flows.
Each cash flow’s PV is computed using PV = (Cash Flow)/(1+i/c)yc where i/c is the yearly rate/number of compounding periods and y are the number of years.
In XNPV , the y is computed as (cash flow number of days from start day)/365.
XIRR Function
XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. XIRR is the discount rate that will make the NPV = 0.
- The XIRR formula is “=XIRR(values, dates, [guess])”
- Values: A series of cash flows that occur on designated dates. The series must contain at least one positive and one negative value.
- Dates: The cash flow dates and can be in any order. Dates should be entered by using the DATE function to avoid errors.
- Guess entry is optional to help speed up the calculation.
Google Sheets Financial Functions
The Google Sheets tool can also be used for TVM calculations.
Its TVM function names and constructs are essentially identical to their Excel counterparts [i.e. NPER(), IRR(), PMT(),FV(),NPV(),PV(), RATE(),IPMT(),PPMT(),CUMIPMT(),CUMPRINC() ].
Manual Calculations Using Excel or Google Sheets
We noted above that the calculator or spreadsheet financial functions cannot do mid year or other “between start-or-end-of-period” cash flows.
For this and other reasons, people (especially corporations) will create their own calculators using spreadsheets or more sophisticated programs.
This way, NPV Models can be built that are tailored specifically for the task at hand (re inflation rates, taxes, specific cash flow timing, ability to do scenario analysis, etc.).
Regardless of the methodology, all NPV calculations compute a PV for each cash flow where the timing factors (the exponents on the compounding factors) must be properly designated.
On Line References
There are lots of good internet articles and videos on how to use Excel and Google Sheet financial function. Listed are a few that I have used before.
Conclusion
Almost all financial decisions require the use and understanding of Time Value of Money (TVM) concepts. This post defines and derives several TVM relationships.
The key learnings from this post are:
- The Compounding Formula for single cash flows is the fundamental building block for all TVM formulas
- Future Value=Present Value(1+period rate)# of periods
- Annuities are constant (or growing) cash flows across a defined time period (see Appendices 6-23 for annuity equation derivations).
- A Timeline should be established and defined for any TVM calculation.
- Indicate cash outflows as negative values if TVM calculator or spreadsheeting functions are used.
- For any kind of analysis or comparative study, cash flows must always be Discounted back and aggregated in the present (Present Value) or Compounded and aggregated in the future (Future Value).
- Never compare cash flows across different time periods.
- Individuals will most likely use one of the Annuity formulas for making financial decisions (e.g. investments, loans, any other financial decision affected by time).
- The key variables that can be solved for in Annuity type problems are: periods, interest, present value, payments, future value, and growth (for Growing Annuity).
- Discounted Cash Flow analysis (DCF) involves computing a Net Present Value (NPV) for a series of cash inflows and outflows.
- If positive, the NPV indicates that more value has been created then the next best investment based on a minimum required rate of return (the Discount Rate).
- Financial calculators (HP and others) and Spreadsheeting tools (Microsoft Excel or Google Sheets) have very useful TVM mathematical functions that can be used for financial planning and decision making.
- Calculator and Spreadsheeting TVM functions require outgoing cash flows (expenses or investments) to be given negative values.
- Perpetuities are Annuities expressed across an infinite period (see the Appendices 18-23 for perpetuity equation derivations).
- These have useful applications but are not discussed in any kind of detail in this post.
Appendix 1 – References
- Damodaran on Time Value of Money
- Harvard Business School Online on Time Value of Money
- Khanacademy introduction-to-interest
- Khanacademy compound-interest
- Khanacademy interest-basics
- Khanacademy Euler’s Number and Continuous Compounding
- General Finance Formulas
- ExcellsFun Finance You Tube Videos
- Actuarial Textbook by Marcel B. Finan: A Basic Course in the Theory of Interest and Derivatives Markets: A Preparation for the Actuarial Exam FM/2
- NYU Stern TVM Primer
- Engineering Economics Guy YouTube Videos
- Engineering Economy: 7th Ed. by Leland Blank and Anthony Tarquin
- Engineering Economic Analysis by Newnan, Eschenbach, Lavelle, Online Text and Video Tutorials
- Bradley Corporate Finance Learning Portal
- Libretexts Business Math Online Text
- Hayashi Manabu YouTube Actuarial Science Tutorials
- AnalystPrep Actuarial (FM) Exam Tutorials by Stephen Paris
- L’hopital’s Rule Khan Academy
Appendix 2 – Sum of a Finite Geometric Series
The equation for a finite geometric series summation is useful in the derivation of the annuity equations.
A Geometric Series can be expressed as,
Sn = a + ab + ab2 +…+abn-1
Where,
- Sn = sum of first n terms
- a = first term
- b = common ratio
- n = number of terms
A finite form of this can be expressed as,
Sn = a(1-bn)/(1-b)
Finite Summation Geometric Series
See a derivation of this equation below.
Appendix 4 – Annuity Equation Conversion factors
An Ordinary Annuity equation can be converted to its Annuity Due form by multiplying it by (1+r) where r is the interest rate.
A Present Value Annuity equation can be converted to it Future Value form by multiplying it by (1+r)n
Appendix 5 – Discount Factor v, Effective Rate of Discount Factor d, and some useful Limit relationships.
Compound(ing) Factor, Discount Factor v, and Effective Rate of Discount Factor d
In actuarial texts, annuities are often expressed using variables v and d. The Discount Factor = v = 1/(1+r) where r is interest rate. The Effective Rate of Discount Factor = d = r/(1+r). See the picture below for various ways these can be expressed.
A Compound Interest Factor is the generic term for the mathematical expression [consisting of r, n, and/or arithmetic or geometric growth rates] that is multiplied against a given variable to compute the desired variable. For example to compute the Present Value of an Ordinary Annuity , the Constant Payment can be multiplied against a compound interest factor called the Uniform Series Present Worth Factor. Compound interest factors are often tabulated in textbooks for easy lookup.
Useful Limits and L’Hopital’s Rule
The limitn→∞1/(1+r)n is useful (= 0) when deriving perpetuity forms of the annuity equations.
Applying L’Hopital’s rule to find a solution to the limit of indeterminate values (e.g. limitn→∞n/(1+r)n which also equals 0) will also be useful when deriving perpetuity forms of annuity equations.
Appendix 6 – Present Value Ordinary Annuity (PVOA)
Annuity payments are constant period payments.
Consider a timeline where constant annuity payments X are made at the end of years 1, 2 ,3 etc.
This kind of annuity is known as an Ordinary Annuity. We want to compute the present value of these payments.
- P = Present Value of all payments
- X = constant payment
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- P1, P2, P3: Present Values X from end of years 1,2,3 etc.
We can discount these three payments to the present and then adjust the equation to arrive at,
P = X/r ( 1 – 1/(1+r)n )
Present Value Ordinary Annuity (PVOA)
See a full derivation below where we develop and generalize the PVOA equation starting with a simple 3 year timeline.
Appendix 7 – Present Value Annuity Due (PVAD)
Annuity payments are constant period payments. Consider constant annuity payments X starting now (time 0, start of year 1), with additional payments at the start of year 2 and 3 etc. (at 1 and 2).
This kind of annuity is known as an Annuity Due. We want to compute the present value of these payments.
- P = Present Value of all payments
- X = constant payment
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- P1, P2, P3: Present Values X from start of years 1,2,3 etc.
We can discount these three payments to the present and then adjust the equation to arrive at,
P = X/r ( 1 – 1/(1+r)n ) (1+r)
Present Value Annuity Due (PVAD)
Note that the Present Value Annuity Due = (Present Value Ordinary Annuity)(1+r)
See a full derivation below where we develop and generalize the PVAD equation starting with a simple 3 year timeline.
Appendix 8 – Present Value Geometric Growing Ordinary Annuity (PVGOAgeom)
Annuity payments are constant period payments. Consider geometric growing annuity payments X, X(1+g), X(1+g)2 etc. starting at end of years 1, 2, 3, etc. respectively.
This kind of annuity is known as an Geometric (exponential) Growing Ordinary Annuity. We want to compute the present value of these payments.
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- P1, P2, P3: Present Values of X, X(1+g), and X(1+g)2 etc. from end of years 1,2,3 etc.
We can discount these payments to the present and arrive at,
P = ( X/(r-g) )( 1-(1+g)n/ (1+r)n ) ; g ≠ r
Present Value Geometric Growing Ordinary Annuity PVGOAgeom
See a full derivation below where we develop and generalize the PVGOAgeom equation starting with a simple 3 year timeline.
Appendix 9 – Present Value Geometric Growing Annuity Due (PVGADgeom)
Annuity payments are constant period payments. Consider geometric growing annuity payments X, X(1+g), X(1+g)2 etc. starting at the start of years 1, 2, 3, etc. respectively.
This kind of annuity is known as a Geometric Growing Annuity Due. We want to compute the present value of these payments.
- P = Present Value of all payments
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- P1, P2, P3: Present Values X, X(1+g), and X(1+g)2 etc. from start of years 1,2,3 etc.
We can discount these payments to the present and then adjust the equation to arrive at,
P = ( X/(r-g) )( 1-(1+g)n / (1+r)n )(1+r) ; g≠r
Present Value Geometric Growing Annuity Due PVGADgeom
Note that Present Value Geometric Growing Annuity Due = (Present Value Geometric Growing Ordinary Annuity)(1+r)
See a full derivation below where we develop and generalize the PVGADgeom equation starting with a simple 3 year timeline.
Appendix 10 – Present Value Arithmetic Growing Ordinary Annuity (PVGOAarith )
An arithmetically growing annuity , grows linearly at a constant value G. A PVGOAarith begins with a constant X paid at the end of year 1, followed by X+G, X+2G, etc.. at the end of subsequent years.
See the timeline and details below for a derivation of the PVGOAarith equation.
Appendix 11 – Present Value Arithmetic Growing Annuity Due (PVGADarith)
An arithmetically growing annuity , grows linearly at a constant value G. A PVGADarith begins with a constant X paid at the beginning of year 1, followed by X+G, X+2G, etc. at the end of subsequent years.
See the timeline and details below for a derivation of the PVGADarith equation. Note that PVGADarith = PVGOAarith(1+r).
Appendix 12 – Future Value Ordinary Annuity (FVOA)
Annuity payments are constant period payments. Consider an annuity, known as an Ordinary Annuity, that pays out X at the end of Years 1, 2, 3, etc.
We want to compute the future value of these payments.
- F = Future Value of all payments
- X = Constant Payment
- r = interest rate per compounding period
- F1, F2, F3: Future Values of X from end of years 1, 2, 3.
- n = number of compounding periods
We can compound these payments to the future and come up with the following equation.
F = X ( (1+r)n – 1) /r
Future Value Ordinary Annuity (FVOA)
See a full derivation below where we develop and generalize the FVOA equation starting with a simple 3 year timeline.
Appendix 13 – Future Value Annuity Due (FVAD)
Consider constant annuity payments X starting now (time 0, start of year 1), with additional payments at the start of year 2 and 3 (at 1 and 2) etc. This kind of annuity is known as an Annuity Due. We want to compute the future value of these payments.
- F = Future Value of all payments
- X = Constant Payment
- r = interest rate per compounding period
- n= number of compounding periods
- F1, F2, F3: Future Values of X from the start of years 1, 2, 3 etc.
F = X ( (1+r)n – 1)(1+r) / r
Future Value Annuity Due (FVAD).
Note that Future Value Annuity Due = (Future Value Ordinary Annuity)(1+r)
See a full derivation below where we develop and generalize the FVAD equation starting with a simple 3 year timeline.
Appendix 14 – Future Value Geometric Growing Ordinary Annuity (FVGOAgeom)
Annuity payments are constant period payments. Consider growing annuity payments X, X(1+g), and X(1+g)2 etc. starting at end of years 1, 2, 3 etc. respectively.
This kind of annuity is known as a Geometric Growing Ordinary Annuity. We want to compute the future value of these payments.
- F = Future Value of all payments
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- F1, F2, F3: Future Values X, X(1+g), and X(1+g)2 etc. from end of years 1,2,3 etc.
We can compound these payments to the future and come up with the following equation.
F = (X/(r-g))((1+r)n-(1+g)n) ; g≠r
Future Value Geometric Growing Ordinary Annuity FVGOAgeom
See a full derivation below where we develop and generalize the FVGOAgeom equation starting with a simple 3 year timeline.
Appendix 15 – Future Value Geometric Growing Annuity Due (FVGADgeom)
Annuity payments are constant period payments. Consider geometric growing annuity payments X, X(1+g), and X(1+g)2 etc. starting at the start of years 1, 2, 3 etc. respectively.
This kind of annuity is known as an Geometric Growing Annuity Due. We want to compute the future value of these payments.
- F = Future Value of all payments
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- n = number of compounding periods (note: n = number of terms in the Sn equation)
- F1, F2, F3 etc.: Future Values X, X(1+g), and X(1+g)2 etc. from start of years 1,2,3 etc.
We can compound these payments to the future and come up with the following equation.
F = ( X/(r-g) )( (1+r)n-(1+g)n )(1+r) ; g≠r
Future Value Geometric Growing Annuity Due FVGADgeom
Note that Future Value Geometric Growing Annuity Due = (Future Value Geometric Growing Ordinary Annuity)(1+r)
See a full derivation below where we develop and generalize the FVGADgeom equation starting with a simple 3 year timeline.
Appendix 16 – Future Value Arithmetic Growing Ordinary Annuity (FVGOAarith)
An arithmetically growing annuity , grows linearly at a constant value G. A FVGOAarith begins with a constant X paid at the end of year 1, followed by X+G, X+2G, etc.. at the end of subsequent years.
See the timeline and details below for a derivation of the FVGOAarith equation.
Appendix 17 – Future Value Arithmetic Growing Annuity Due (FVGADarith)
An arithmetically growing annuity , grows linearly at a constant value G. A FVGADarith begins with a constant X paid at the beginning of year 1, followed by X+G, X+2G, etc. at the end of subsequent years.
See the timeline and details below for a derivation of the FVGADarith equation. Note that FVGADarith = FVGOAarith(1+r).
Appendix 18 – Present Value Ordinary Perpetuity (PVOP)
A perpetuity is an annuity that goes on forever. Consider an Ordinary Perpetuity that pays out X at the end of each year.
Let’s derive the Present Value Ordinary Perpetuity equation.
- P = Present Value of all payments
- X = constant payment
- r = interest rate per compounding period
- P1, P2, P3 etc.: Present Values X from end of years 1,2,3 etc.
After discounting the terms and doing a few simple mathematical manipulations to the resultant infinite series we get,
P = X/r
Present Value Ordinary Perpetuity (PVOP)
See a full derivation below:
Appendix 19 – Present Value Perpetuity Due (PVPD)
A perpetuity is an annuity that goes on forever. Consider a Perpetuity Due that pays out X at the start of each year.
Let’s derive the Present Value Perpetuity Due equation.
- P = Present Value of all payments
- X = constant payment
- r = interest rate per compounding period
- P1, P2, P3 etc.: Present Values X from start of years 1,2,3 etc.
After discounting the terms and doing a few simple mathematical manipulations to the resultant infinite series we get,
P = (1+r)X/r
Present Value Perpetuity Due (PVPD)
Note that the Present Value Perpetuity Due = (Present Value Ordinary Perpetuity)(1+r)
See a full derivation below:
Appendix 20 – Present Value Geometrically Growing Ordinary Perpetuity (PVGOPgeom)
Geometric Growing Perpetuity payments are constant period payments that grow exponentially and go on forever.
Consider geometrically growing payments X, X(1+g), and X(1+g)2 etc. starting at end of years 1, 2, etc. respectively (a Geometric Growing Ordinary Perpetuity).
We want to compute the present value of these payments.
- P = Present Value of all payments
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- P1, P2, P3 etc.: Present Values X/(1+r), X(1+g)/(1+r)2, and X(1+g)2 /(1+r)3 etc. from end of years 1,2,3 etc.
After discounting the terms and doing a few simple mathematical manipulations to the resultant infinite series we get,
P = X/(r-g) ; g<r
Present Value Growing Ordinary Perpetuity PVGOPgeom
See a full derivation below:
Appendix 21 – Present Value Geometrically Growing Perpetuity Due (PVGPDgeom)
Perpetuity payments are annuities that go on forever. Consider geometrically growing annuity payments X, X(1+g), and X(1+g)2 etc.. starting at the start of years 1, 2, 3 etc. respectively.
This kind of annuity is known as a Geometrically Growing Perpetuity Due. We want to compute the present value of these payments.
- P = Present Value of all payments
- X = constant payment
- g = constant growth rate of X
- r = interest rate per compounding period
- P1, P2, P3: Present Values X, X(1+g)/(1+r), and X(1+g)2 /(1+r)2 from start of years 1,2, etc.
After discounting the terms and doing a few simple mathematical manipulations to the resultant infinite series we get,
P = (1+r)X/(r-g) ; g<r
Present Value Growing Perpetuity Due PVGPDgeom
Note that Present Value Geometrically Growing Perpetuity Due = (Present Value Geometrically Growing Ordinary Perpetuity)(1+r)
See a full derivation below:
Appendix 22 – Present Value Arithmetically Growing Ordinary Perpetuity (PVGOParith)
Arithmetically Growing Perpetuity payments are constant period payments that grow linearly and go on forever.
Consider arithmetically growing payments X, X+G, X+2G etc. starting at end of years 1, 2, 3 etc. respectively (an Arithmetically Growing Ordinary Perpetuity).
We want to compute the present value of these payments.
We can derive this by starting with the equation for a PVGOAarith and letting n →∞.
P = X/r + G/r2
Present Value Arithmetically Growing Ordinary Perpetuity PVGOParith
See a full derivation below:
Appendix 23 – Present Value Arithmetically Growing Perpetuity Due (PVGPDarith)
Arithmetically Growing Perpetuity payments are payments that grow linearly and go on forever. Consider arithmetically growing annuity payments X, X+G, and X+2G etc. starting at the start of years 1, 2, 3 etc. respectively. Both X and G are constants. We want to compute the present value of these payments.
We can derive this by starting with the equation for a PVGADarith and letting n →∞.
P = X(1+r)/r + G(1+r)/r2
Present Value Growing Perpetuity Due PVGPDarith
Note that PVGPDarith = PVGOParith(1+r)
See a full derivation below:
Appendix 24 – Continuous Compounding Formula Derivation
Let’s derive the equation for continuous (infinite) compounding.
Consider the expression (1+1/c)c
The table below shows that as c increases, the expression approaches a value called Euler’s Number, e.
Euler’s Number is an amazing number, and it will figure into our infinite compounding derivation, but first let’s take a little diversion.
A Diversion
Euler’s number is a non-terminating number (endless) and it can’t be expressed as a fraction (i.e. it’s an irrational number).
Its description as “e” was used by Swiss mathematician Leonhard Euler (pronounced “Oiler”) in ~1731.
So Euler coined the constant e and popularized it, but
- the constant was used by British Mathematician John Napier in ~1614 and
- was first discretely described by Jacob Bernoulli (another famous Swiss mathematician!). (Source: Wikipedia).
Some cool facts that are not really relevant to our derivation , but , need to be mentioned, because you should be amazed:
- Euler’s can be defined a few other ways. Here is one of those:
- c! = c”factorial” meaning: For c>0, c! = 1×2×3×4×…×c For n=0; 0! = 1
- Another non-terminating and irrational number is Pi (π = 3.14159…)
- And, for those who argue there is order in the universe, e and π are related through the Euler Identity.
- The Euler Identity: eiπ +1 = 0
- You really should watch Sal Khan’s (khanacademy) derivation of the Euler Identity: Start with his video on the imaginary unit i and then view his video on Euler’s formula and Euler’s Identity.
Ok back to the task at hand.
Continuous (Infinite) Compounding Equation Derivation
Refer to the great tutorials by Sal Khan on how to derive the infinite compounding formula.
Remember the Compound Interest Formula for a Single Cash Flow where,
- F = P(1+r)n = P(1+i/c)cy
- F = Future Value, P = Present Value,
- i = yearly interest rate (sometimes called the stated rate)
- c = number of compounding periods per year
- r = interest rate per compounding period = i/c
- y = number of years
- n = total number of compounding periods = (c)(y)
Start with our compounding formula.
(1) F = P(1+i/c)cy
Assume c is going to infinity. Then
(2) F = lim(c→∞) [ P(1+i/c)cy ]
Now we want to substitute and let x = c/i. So c = xi and i/c = 1/x.
Substitute for c and i/c in equation (2). We get,
(3) F = lim(x→∞) [ P(1+1/x)xiy ] which equals
(4) F = P [ lim(x→∞) (1+1/x)x ] iy . The limit expression in this equation we know is equal to e, Euler’s number, so,
(5) F = P e iy = Equation for Continuous Compounding
Very nice.
