View source for Comparing investments

'''{{PAGENAME}}''' shows how to compare the ''past performance'' of two investments. Solutions are provided to allow the reader to duplicate the results with a spreadsheet.

There are many online financial calculators that do exactly what this article describes. However, the reader is placing full confidence that the math behind the online calculator (1) is accurate and (2) uses assumptions expected by the reader. There should be no surprises, especially when finances are involved. Having your own spreadsheet clearly defines the situation. What-if scenarios are easily done, especially since the data can be manipulated to see trends with a simple chart.
{{Notice|
Past performance is no guarantee of future performance. Interpretation of results is the responsibility of the reader. If there are any questions, please don't hesitate to ask in the forum for guidance.
}}
Microsoft Excel was used for this article. However, [https://www.libreoffice.org/discover/calc/ LibreOffice Calc] or [http://www.openoffice.org/product/calc.html Open Office Calc] will work equally well and are free applications supported on several operating systems. Financial functions are very similar to Microsoft's functions.<ref>[http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_Financial_functions Open Office Calc Financial functions]. For example PV(rate; numperiods; payment; futurevalue; type) is identical.</ref>

Copy the formulas from the example tables below and paste into a spreadsheet. Alternatively, the examples and graphs are available for download at the bottom of [[:#External links | this page]].

==Present value==
In order to evaluate a financial investment, prices are referenced as if the money is invested ''today''. The amount of money that must be invested ''today'' is called ''present value''. Using the same point in time allows comparison between different financial instruments, e.g. an "apples-to-apples" comparison.

The equation for Present Value can be expressed as<ref>[http://www.investopedia.com/articles/03/082703.asp Understanding The Time Value Of Money]</ref>:
:{| style="border:1px solid darkgray;"
|align="left" |'''PV = FV * 1 / (1 + i)<sup>N</sup>''' -or- '''PV = FV * (1 + i)<sup>-N</sup>'''
|}
where
:''PV'' is the present value (amount of money today)
:''FV'' is the future value (amount of money at some point in the future)
:''i'' is the interest rate
:''N'' is the number of periods

The graph below shows the general idea. The slope of the line depends on the interest rate (i) and the number of payments (N).

<!-- graphs look better left aligned, double indented, add "class="wikitable" to center --> 
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Equate Future Prices to the Present'''
||[[File:Reference future values to present value.png|400px|center]]
|}

===Fundamental property #1===
The first important property of ''present value'' is that the higher the interest rate (or discount rate<ref>Calculating the ''present value'' is also referred to as ''discounting''. The interest rate is referred to as the ''discount rate'' and ''present value'' is also known as ''discounted value''.</ref>), the lower the present price.<ref>Fabozzi, Frank J., "Fixed Income Mathematics, Analytical & Statistical Techniques", [http://www.amazon.com/Fixed-Income-Mathematics-Frank-Fabozzi/dp/0786311215?tag=bogleheads.org-20 3rd edition], page 22.<br> Note: The graphs on this page are not from this text (derived by the wiki author). There are no spreadsheet programs used in this book- the examples are used to verify results from Excel.</ref>

The higher the interest rate, the less that has to be invested today to achieve the same value in the future.  

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Increasing the interest rate lowers the price.'''
||[[File:Increases in the interest rate lowers the price.png|400px|center]]
|}

===Fundamental property #2===
The second important property of ''present value'' is that for a given interest rate (or discount rate), the farther into the future the investment will be received, the lower the present value.<ref>Fabozzi, page 22.</ref>

Longer term investments have more time for the interest to accumulate, resulting in fewer dollars that need to be invested.

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''The longer the investment time, the lower the price.'''
||[[File:Longer investement time - lower price.png|400px|center]]
|}

==How to compare investments==
Every financial calculation involves certain basic elements; but the terminology used to refer to these elements typically differs among the various segments of the business and financial communities. All you need to do is identify the basic elements of the problem, and then structure the problem so that it will be readily apparent what quantities you should enter into Excel and what quantity you want to solve for.<ref name="HP">[http://h10032.www1.hp.com/ctg/Manual/c00363319.pdf HP-12C Calculator User's Guide], page 34. Financial Calculations and the Cash Flow Diagram (updated by wiki author for Excel)]</ref>

The process is basically:
#Sum the individual cash flows (every payment and any final value) in today's price for each investment.
#Compare the prices.

Use a cash flow diagram to help setup the problem and identify what's needed. Then, use Excel to bring the future cash flows back to today's price (present value).

An investment that consists of equal payments is referred to as an ''annuity''.<ref name="HP"></ref> Investments with ''unequal'' payments need to use different Excel functions (NPV and IRR) and are discussed [[:#Unequal payments | below]].

===Cash flow diagrams===
{{quote
|quote = 
Any financial problem can be solved by:
#Drawing the cash flow diagram
#Defining the problem in terms of 4 financial variables
#Using Excel to solve for the 5th (unknown) variable.

Excel provides a function for each variable:
*PV - use PV()
*FV - use FV()
*PMT - use PMT()
*N - use NPER()
*I - use RATE()
}}
A cash flow diagram is a graphical representation of the timing and direction of financial transactions. The diagram begins with a horizontal line, called a time line. It represents the
duration of a financial problem, and is divided into compounding periods.

The exchange of money in a problem is depicted by vertical arrows. Money you receive is represented by an arrow pointing up from the point in the time line when the transaction occurs; money you pay out is  represented by an arrow pointing down.

<!-- graphs look better left aligned, double indented, add "class="wikitable" to center --> 
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Cash Flow Diagram'''
||[[File:Cash flow diagram.png|400px|center]]
|}

Solving the problem is now basically a matter of entering the quantities identified in the cash flow diagram into Excel.

Every completed cash flow diagram must include at least one cash flow in each direction. Note that cash flows corresponding to the accrual of interest are not represented by arrows in the cash flow diagram.<ref name = "HP"></ref>

Complicated problems may require decomposition into several simpler cash flows. The total cash flow is always equal to the sum of the individual cash flows. This principal holds true for all investments. As long as the cash flow diagram is drawn correctly, the problem can be solved.<ref name="dratkinson"></ref>

===Sign convention===
Financial functions use a standard sign convention to indicate the direction of [[:#Cash Flow | cash flow]].<ref name="dratkinson">From dratkinson via PM.</ref>

*A positive sign is used for money that flows into our hand.
*A negative sign is used for money that flows out of our hand.

Whether a cash flow is an inflow (+) or outflow (-) depends on your perspective. For example, money paid on a loan (outflow) is negative.<ref>Excel help for PV().</ref>

<u>Note:</u>
*One wrong sign will change the computed results and give an incorrect answer.
*If you consistently change all signs in the problem, you switch roles (for example, switching from being the borrower to being the lender)
*In all problems, both signs must be used. Sometimes the other sign is in the answer. 

===Financial variables===
There are 5 financial variables.<ref>[http://h10025.www1.hp.com/ewfrf/wc/document?docname=bpia5185&cc=us&dlc=en&lc=en Time Value of Money (TVM) Calculation], from hp.com.</ref>

#'''PV''': The first unequal money in or out of the cash flow diagram is the Present Value.
#'''FV''': The last unequal money in or out of the cash flow diagram is the Future Value.
#'''PMT''': A fixed amount received in each period is the Payment.
#'''N''': Number of periods.
#'''I''': Interest rate per compounding period.

''N'' and ''I'' must be expressed in the same terms. If N is years, then I must be expressed in years. If N is months, then I must be expressed in months. 

====Type====
When working with periodic payments, it's important to know whether the payment is due at the beginning or end of a compounding period. To accommodate the payment due dates, spreadsheet functions include a parameter known as ''Type'', which is defined as:

*0 or omitted: Payments are due at the end of the period (payment in arrears, or ''ordinary annuity'')
*1: Payments are due at the beginning of a compounding period (payment in advance, or ''annuity due'')

Calculations involving payments in advance yield different results than calculations involving payments in arrears.

<!-- graphs look better left aligned, double indented, add "class="wikitable" to center --> 
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Cash Flow Diagram'''
||[[File:Cash flow - when the payment is made.png|400px|center]]
|}

==Comparing interest rates==

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Compare the offered price to the present value.'''
||[[File:Compare offered price to present value.png|400px|center]]
|}

===Example 1===
An investing opportunity offers to pay $500 at the end of each year for the next 20 years. At the end of 20 years, the value of this investment is zero, it can not be sold.

For example: You loan your friend some money. In 20 years when the debt is repaid, its value is zero.

The purchase price of this opportunity is $5,300.

If the investor wants a 5.5% interest rate, is this a good investment?<ref>Fabozzi, page 25. Comments and detailed solution from dratkinson via PM.</ref>

The cash flow diagram looks like this:

<!-- graphs look better left aligned, double indented, add "class="wikitable" to center --> 
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Cash Flow Diagram'''
||[[File:Investment Example 1.png|400px|center]]
|}

*I = 5.5 (this is the APY)
*N = 20 (each period is one year)
*PMT = 500 (sign is positive because money flows into our hand)
*FV = 0 (there is no last period unequal amount received)
*Type = 0 (end of period payments)
*PV = (solve for this) 

''With Excel:''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|align = "center"|'''Present Value'''
|align = "center"|'''Excel Formula'''
|-
|align = "center" style="color:red"|($5,975.19)
|align = "center"|=PV(5.5%,20,+500)
|}

The PV of this investment is $5,975.19. The sign is negative because we should pay (money out of our hand) this much today to buy this investment. Forgetting the sign, this investment is worth $5,975.19.

Since we can buy an investment worth $5,975.19 for only $5,300, it is a good investment. 

===Example 2===
An investing opportunity offers to pay $100 per year for the next 5 years and an additional $1,000 at the end of 5 years. The purchase price is $1,243.83.

If the investor wants a 6.25% interest rate, is this a good investment?<ref>Fabozzi, page 24. Detailed solution from dratkinson via PM.</ref>

<!-- graphs look better left aligned, double indented, add "class="wikitable" to center --> 
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Cash Flow Diagram'''
||[[File:Investment Example 2.png|600px|center]]
|}

This example has 2 cash flows. It could either be drawn in a single diagram or as two separate cash flows - one for the periodic payments and one for the final payment. The summation of the 2 cash flows is identical to the original solution. [[:#Example 6 - Net Present Value | Example 6]] is a more comprehensive example which uses separate cash flows.

*I = 6.25 (this is the APY)
*N = 5 (each period is one year)
*PMT = 100 (sign is positive because money flows into our hand)
*FV = 1000 (sign is positive because money flows into our hand)
*Type = 0 (end of period payments)
*PV = (solve for this) 

''Excel (displayed):''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| |&nbsp;
|align = "center"|'''Present Value<br>Column D'''
|align = "center"|'''Present Value<br>(Combined)'''
|-
|align = "center"|'''Row 8'''
|align = "right"|<font color="#FF0000">($418.39)</font>
|align = "center"|&nbsp;
|-
|align = "center"|'''Row 9'''
|align = "right"|<font color="#FF0000">($738.51)</font>
|align = "center"|&nbsp;
|-
|align = "right"|'''Sum:'''
|align = "right"|<font color="#FF0000">($1,156.90)</font>
|align = "center"|<font color="#FF0000">($1,156.90)</font>
|}

''Excel formulas:''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"

||&nbsp;
|align = "center" |'''Present Value<br>Column D'''
| |'''Present Value<br>(Combined)'''
|-
|align = "center"|'''Row 8'''
|=PV(6.25%,5,100,0,0)
|&nbsp;
|-
|align = "center"|'''Row 9'''
|=PV(6.25%,5,0,1000,0)
|&nbsp;
|-
|align = "right"|'''Sum:'''
|=SUM(D8:D9)
|align = "center"|=PV(6.25%,5,100,1000,0)
|}

Excel's PV function allows the future value to be entered along with the payment. This problem could be solved either as two separate entries (sum individual cash flows, column on left) or a single equation (column on right). In either case, the result is identical. It all depends on how the cash flow diagram is setup.

The PV of this investment is $1,156.90. Remember the negative sign only tells us that the money flows out of our hand to buy it, nothing more. Ignore the sign, this investment is worth $1,156.90.

As the investment is worth less than the purchase price ($1,156.90 is less than $1,243.83), this is not a good investment.

===Example 3===
Would you like to receive $15,000 today or $18,000 in four years?

The answer depends on the interest rate. If you can find an investment with good interest rate, it will be better to take the $15,000 today. How do you find the interest rate to invest $15,000 today?

Use a starting guess of 4%. The present value of $18,000 received four years from now is $15,386.48. Since the present value is higher than $15,000 (receiving $386.48 more) it is still better to receive $18,000 in four years.<ref>From [http://www.investopedia.com/articles/03/082703.asp Understanding The Time Value Of Money], but extended to show the break-even point.</ref>

''With Excel:''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| align = "center"|'''Present Value'''
| align = "center"|'''Excel Formula'''
|-
|align = "center"|$15,386.48 
|align = "center"|=PV(4%,4,0,-18000)
|}

Now, let Excel remove the guess work and find where it would be more beneficial to receive the $15,000 now.

Use the ''Goal Seek'' function (see Excel help for details) to set the value of Cell F13 to zero by adjusting Cell C13.

''Excel (displayed):''
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| align = "center"|Rate
| align = "center"|PV
| align = "center"|Purchase Price
| align = "center"|Difference
|-
|4.0%
|$15,386.48 
|$15,000.00 
|$386.48 
|-
|'''4.7%'''
|$15,000.00 
|$15,000.00 
|'''$0.00 '''
|}

''Excel formulas:''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| align = "center"|&nbsp;
| align = "center"|'''Rate<br>(Column C)'''
| align = "center"|'''PV<br>(Column D)'''
| align = "center"|'''Purchase Price<br>(Column E)'''
| align = "center"|'''Difference<br>(Column F)'''
|-
|'''Row 12'''
| align = "right"|0.04
|=PV(C12,4,0,-18000)
| align = "center"|15000
|=D12-E12
|-
|'''Row 13'''
| align = "right"|'''0.0466'''
|=PV(C13,4,0,-18000)
| align = "center"|15000
|'''=D13-E13'''
|}

If you can get more than 4.7% for your investment now, take the $15,000.

==Comparing interest rates and maturity dates==

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|+'''Compare with different interest rates and maturity dates.'''
||[[File:Compare two investments.png|400px|center]]
|}

===Example 4===
Which is better: $140,000 paid over the next 36 months or $160,000 paid over the next 60 months? Clearly $160,000 is better than $140,000 but what is the interest rate on the deferred funds to get the additional $20,000?<ref name="BH1">[http://www.bogleheads.org/forum/viewtopic.php?p=666701 $140,000 over 3 years or $160,000 over 5 years]</ref>

Phrased differently, what is the interest rate for the first investment (36 months) to match the 60 month investment ($160,000)?

The interest rate where it would be worthwhile to take the $140,000 in 36 months is 14.90%.

How:

Find the interest rate where the present value of both investments are equal. In other words, make the present value of the two investments in the figure above equal height by adjusting the interest rate (ignore the FV height difference, the graph is easier to read this way). Adjust the interest rate for both investments simultaneously to find where the arrows align.

Let Excel do the work. Use the ''Goal Seek'' function (see Excel help for details) to set the value of Cell P11 to zero by adjusting Cell K11.

''Displayed values:''

::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| align = "center"|'''Effective<br>Annual Yield'''
| align = "center"|'''Nominal<br>Interest Rate'''
| align = "center"|'''Monthly<br>Interest Rate'''
| align = "center"|'''36 Month Investment'''
| align = "center"|'''60 Month Investment'''
| align = "center"|'''Difference'''
|-
|align = "right"|15.96%
|align = "right"|14.90%
|align = "right"|1.24%
| align = "center"|$112,346.88 
| align = "center"|$112,346.88 
| align = "center"|($0.00)
|}

''Cell Formulas:''
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| align = "center"|&nbsp;
| align = "center"|'''Effective<br>Annual Yield<br>(Column K)'''
| align = "center"|'''Nominal<br>Interest Rate<br>(Column L)'''
| align = "center"|'''Monthly<br>Interest Rate<br>(Column M)'''
| align = "center"|'''36 Month Investment<br>(Column N)'''
| align = "center"|'''60 Month Investment<br>(Column O)'''
| align = "center"|'''Difference<br>(Column P)'''
|-
| align = "center"|'''Row 11'''
|align = "right"|0.15957
|=NOMINAL(K11,12)
| align = "center"|=L11/12
| align = "center"|=PV(M11,36,-140000/36)
| align = "center"|=PV(M11,60,-160000/60)
| align = "center"|=N11-O11
|}

There is an important assumption that exemplifies the need for caution. Nowhere does it say that the interest is an annual rate! This problem is stated in ''months'', and the solution is a rate per ''month''.<ref name="BH1"></ref> That's how the PV() equation works. All time periods must be the same. If the reader interpreted this as annual rate, a significant error of 1.06% results.

There are 2 columns of interest here. ''Effective Annual Yield'' and ''Nominal Interest Rate'', which are explained [[:#Periodic Interest Rate | below]]. The ''Goal Seek'' function was used to find the annual yield, which flows into the monthly rate.

==Comparing mortgages==
Building on the above concepts, forum member jimb_fromATL has a comprehensive tutorial for calculating and comparing mortgages.

See this forum post: [http://www.bogleheads.org/forum/viewtopic.php?p=1906212#p1906212 Analytical solution for annual mortgage interest?]

==Compounded interest rates==
As shown in [[:#Example 4 | Example 4]], the time period and type of interest (compounded<ref>[http://www.investopedia.com/terms/c/compounding.asp Compounding], on Investopedia</ref> or simple) needs to be clearly defined. Use Excel to convert between an annual yield and the rate for the period of interest.

===APR versus APY===
There are two ways of expressing compound interest. Although the results are identical, the manner in which the interest rates are presented has implications to borrowers and lenders.<ref>[http://www.investopedia.com/articles/basics/04/102904.asp APR and APY: Why Your Bank Hopes You Can't Tell The Difference], from [http://www.investopedia.com/ Investopedia]</ref> As defined below, Annual Percentage Rate (APR) is always a lower number than Annual Percentage Yield (APY).

*Lenders, such as banks or other institutions, will quote the higher APY to entice customers. For example, bank savings accounts are advertised using APY, as customers are looking for the highest rates.
*Borrowers looking for the lowest rates will be attracted to the lower APR. For example, mortgages and automobile loans use APR to entice borrowers with low rates.

To find the total interest paid over the life of a loan, use Excel's [http://office.microsoft.com/en-us/excel-help/cumipmt-function-HP010062353.aspx CUMIPMT()] function, not FV().

===Periodic interest rate===
By definition, the periodic rate is the annual rate divided by the number of periods per year. So multiply the periodic rate by the number of periods per year to get the annual rate.<ref name = "BH2">[http://www.bogleheads.org/forum/viewtopic.php?p=686869 New in Wiki - Comparing Investments], forum discussion</ref> For example, if we have an annual interest rate and want to find the semiannual interest rate, just divide the annual rate by 2.

To determine the APY from the APR or vice versa, first compute the ''Periodic Interest Rate'' using one of the first two formulas below; and then compute the other using one of the next two formulas.

:{| 
|style="border:1px solid darkgray;" |
Period Interest Rate = APR / N<br>
Period Interest Rate = (1 + APY)<sup>(1 / N)</sup> - 1<br>
<br>
APR = Period Interest Rate * N<br>
APY = (1 + Period Interest Rate)<sup>N</sup> - 1<br>
<br>
Where:
:APR = Annual Percentage Rate
:APY = Annual Percentage Yield
:N   = number of compounding periods per year
:&nbsp;&nbsp;&nbsp;&nbsp;= 2 for semi-annual compounding, 4 for quarterly, 12 for monthly, 365 for daily
|}

Note: Excel functions [[:#Excel Functions | RATE]] and [[:#Excel Functions | IRR]] return the ''periodic rate'', while [[:#Excel Functions | XIRR]] returns the ''annualized yield''.<ref name="BH2"></ref>

===Yield===
Yield is defined as the return on an ''investment''. It is computed by determining the ''interest rate'' that will make the present value of the cash flow from an investment equal to its price. This is shown mathematically as:
:{| style="border:1px solid darkgray;"
|align="left" |p = C<sub>1</sub>/(1 + y) + C<sub>2</sub>/(1 + y)<sup>2</sup> + C<sub>3</sub>/(1 + y)<sup>3</sup> + ... +C<sub>N</sub>/(1 + y)<sup>N</sup>
|}

where
:p = Price
:C<sub>t</sub> = Cash flow in year ''t''
:y = <u>Yield</u> (interest rate used to determine the price of the investment)
:N = Number of years

If the equation is confusing, just remember that ''interest rate is used to determine the yield'' of an investment. For example, [[:Certificate of Deposit | CDs]] often quote both the periodic interest rate and the effective annual yield. The higher interest rate is the effective annual yield.

The yield calculated above is also called the ''internal rate of return''.<ref>Fabozzi, page 34.</ref>

===Approximating the interest rate===
More often than not, monthly interest rates are obtained from the effective annual yield by simply dividing by the number of periods. For example, a monthly interest rate is usually taken as the annual rate / 12. 

For typical interest rates, simple multiplication is often close enough to the [[:#Periodic Interest Rate | exponential equation]]. It produces the result with a lot less work if you don't have a calculator that can do exponential and logarithmic functions.

For example, 3% per year is 0.25% per month by division, and 0.246627% per month by the exponential formula- a difference of only 0.0034 percentage points.

Going the other way, 0.25% per month is 3% by multiplication or 3.041596% by exponential formula.

It's possible that the use of simple multiplication in certain contexts (such as monthly vs. annual interest rates) is traditional and dates back to the pre-calculator era.<ref name="BH2"></ref>

====Consequences of approximation errors====
Caution is advised when approximating interest rates for large amounts of funds over long periods of time.

For example, a home mortgage is available for an annual interest rate of 12%. However, it's not clearly stated whether the rate is compounded yearly or monthly. The difference is only

:{| style="border:1px solid darkgray;"
|align="left" |0.0511 % per month = 1 % (12% / 12) - 0.949% (NOMINAL(0.12,12)/12)
|}

Compounding over 16 years results in an overpayment by the homeowner of several thousand dollars. See the [http://www.bogleheads.org/forum/viewtopic.php?p=686899#686899 forum discussion] for details. One suggested solution is included with the Excel file at the bottom of [[:#External Links | this page]].

===Example 5===
Show the monthly interest rates for a given effective annual yield. Conversely, show the effective annual yield for a monthly interest rate.

Displayed values:
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| width="10%"|&nbsp;
|align = "center" |'''Annual Yield<br>(Column K)'''
|align = "center" |'''Nominal Monthly Rate'''
|align = "center" |&nbsp;
|align = "center" |'''Monthly Rate<br>(Column N)'''
|align = "center" |'''Effective Annual Yield'''
|-
|align = "center"|'''Row 22'''
|align = "right"|10%
|align = "center"|0.80%
|&nbsp;
|align = "right"|2.0%
|align = "center"|26.82%
|-
|align = "center"|'''Row 23'''
|align = "right"|8%
|align = "center"|0.64%
|&nbsp;
|align = "right"|1.5%
|align = "center"|19.56%
|-
|align = "center"|'''Row 24'''
|align = "right"|6%
|align = "center"|0.49%
|&nbsp;
|align = "right"|1.1%
|align = "center"|14.03%
|-
|align = "center"|'''Row 25'''
|align = "right"|4%
|align = "center"|0.33%
|&nbsp;
|align = "right"|1.0%
|align = "center"|12.68%
|-
|align = "center"|'''Row 26'''
|align = "right"|2%
|align = "center"|0.17%
|&nbsp;
|align = "right"|0.5%
|align = "center"|6.17%
|}

Cell formulas:
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
| width="10%"|&nbsp;
|align = "center" |'''Annual Yield<br>(Column K)'''
|align = "center" |'''Nominal Monthly Rate'''
|align = "center" |&nbsp;
|align = "center" |'''Monthly Rate<br>(Column N)'''
|align = "center" |'''Effective Annual Yield'''
|-
|align = "center"|'''Row 22'''
|align = "right"|0.1
|=NOMINAL(K22,12)/12
|&nbsp;
|align = "right"|0.02
|align = "center"|=EFFECT(N22*12,12)
|-
|align = "center"|'''Row 23'''
|align = "right"|0.08
|=NOMINAL(K23,12)/12
|&nbsp;
|align = "right"|0.015
|align = "center"|=EFFECT(N23*12,12)
|-
|align = "center"|'''Row 24'''
|align = "right"|0.06
|=NOMINAL(K24,12)/12
|&nbsp;
|align = "right"|0.011
|align = "center"|=EFFECT(N24*12,12)
|-
|align = "center"|'''Row 25'''
|align = "right"|0.04
|=NOMINAL(K25,12)/12
|&nbsp;
|align = "right"|0.01
|align = "center"|=EFFECT(N25*12,12)
|-
|align = "center"|'''Row 26'''
|align = "right"|0.02
|=NOMINAL(K26,12)/12
|&nbsp;
|align = "right"|0.005
|align = "center"|=EFFECT(N26*12,12)
|}

==Unequal payments==

The present value formula can only be used if all payments are the same amount. Suppose you have a series of payments that vary every period? The theory is the same- sum the present value of the individual cash flows (payments). The only difference is that you need to use a ''present value'' function for each payment.

===Example 6 - net present value===
Suppose you have the opportunity to purchase an investment which promises to make the following payments every quarter (3 months, see table below). If a 12% interest rate is desired, what is the most that you should pay for it?

You should pay at most $8,212.79.<ref>Fabozzi, page 28.</ref>

How:

First, note that neither the type of interest nor the period of time is stated. For this problem, it's 12 % per ''year'', but the quarterly interest is defined as 3% = 12% / 4. This is simple interest, no compounding is used.

Since the payments vary every 3 month period, the present value of each payment is calculated. Then, the payments are summed to get the total ''present value''. Excel already has a function that will do the individual calculations for you, called ''Net Present Value''. Note that the answers match (Sum of Column F with Net Present Value in Cell G20).

Displayed values:
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|align = "center" |&nbsp;
|align = "center" |'''Interest Rate<br>(Quarterly) (Column C)'''
|align = "center" |'''3 Month Period<br>(Column D)'''
|align = "center" |'''Payment<br>(Column E)'''
|align = "center" |'''Present Value<br>(Column F)'''
|align = "center" |'''Net Present Value<br>(Column G)'''
|-
|align = "center"|'''Row 20'''
|align = "center"|0.03
|align = "center"|1
|align = "center"|1000
|align = "right"|$970.87 
|align = "right"|'''$8,212.79''' 
|-
|align = "center"|'''Row 21'''
|align = "center"|0.03
|align = "center"|2
|align = "center"|1200
|align = "right"|$1,131.12 
|align = "right"|&nbsp;
|-
|align = "center"|'''Row 22'''
|align = "center"|0.03
|align = "center"|3
|align = "center"|1500
|align = "right"|$1,372.71 
|align = "right"|&nbsp;
|-
|align = "center"|'''Row 23'''
|align = "center"|0.03
|align = "center"|4
|align = "center"|1700
|align = "right"|$1,510.43 
|align = "right"|&nbsp;
|-
|align = "center"|'''Row 24'''
|align = "center"|0.03
|align = "center"|5
|align = "center"|1800
|align = "right"|$1,552.70 
|align = "right"|&nbsp;
|-
|align = "center"|'''Row 25'''
|align = "center"|0.03
|align = "center"|6
|align = "center"|2000
|align = "right"|$1,674.97 
|align = "right"|&nbsp;
|-
|&nbsp;
|&nbsp;
|&nbsp;
|align = "right"|'''Sum of Present Value:'''
|align = "right"|'''$8,212.79''' 
|align = "right"|&nbsp;
|}

Cell formulas:
::{| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"
|align = "center" |&nbsp;
|align = "center" |'''Interest Rate (Quarterly)<br>(Column C)'''
|align = "center" |'''3 Month Period<br>(Column D)'''
|align = "center" |'''Payment<br>(Column E)'''
|align = "center" |'''Present Value<br>(Column F)'''
|align = "center" |'''Net Present Value<br>(Column G)'''
|-
|align = "center"|'''Row 20'''
|align = "center"|0.03
|align = "center"|1
|align = "center"|1000
|align = "center"|=PV(C20,D20,0,-E20)
|align = "center"|=NPV(C20,E20:E25)
|-
|align = "center"|'''Row 21'''
|align = "center"|0.03
|align = "center"|2
|align = "center"|1200
|align = "center"|=PV(C21,D21,0,-E21)
|align = "center"|&nbsp;
|-
|align = "center"|'''Row 22'''
|align = "center"|0.03
|align = "center"|3
|align = "center"|1500
|align = "center"|=PV(C22,D22,0,-E22)
|align = "center"|&nbsp;
|-
|align = "center"|'''Row 23'''
|align = "center"|0.03
|align = "center"|4
|align = "center"|1700
|align = "center"|=PV(C23,D23,0,-E23)
|align = "center"|&nbsp;
|-
|align = "center"|'''Row 24'''
|align = "center"|0.03
|align = "center"|5
|align = "center"|1800
|align = "center"|=PV(C24,D24,0,-E24)
|align = "center"|&nbsp;
|-
|align = "center"|'''Row 25'''
|align = "center"|0.03
|align = "center"|6
|align = "center"|2000
|align = "center"|=PV(C25,D25,0,-E25)
|align = "center"|&nbsp;
|-
|&nbsp;
|&nbsp;
|&nbsp;
|align = "right"|'''Sum of Present Value:'''
|align = "center"|=SUM(F20:F25)
|align = "center"|&nbsp;
|}

===Internal rate of return===
Both ''Present Value'' and ''Net Present Value'' assume that the interest rate is defined. If you only have the payments, how do you calculate the interest?

Not a problem. Just use the Internal Rate of Return function, [[:#Excel Functions | IRR]], to find the yield (internal rate of return). Finding the solution is somewhat more complicated and best left to Excel. Refer to Microsoft's help for information on the IRR function. Pay special attention to setting up the problem. The sum of the income and payments must be zero for the function to work.

For investments that are ''not'' periodic, where there are arbitrary contributions and withdrawals, use [[:#Excel Functions | XIRR]].

[[:#Excel Functions | XIRR]] is the best and most accurate method for calculating your rate of return, if you know the dates you made your investment contributions and your withdrawals. This method is far more accurate than annual average returns, as contributions and withdrawals (cash flows) are weighted for both time and value.<ref>[http://www.bogleheads.org/forum/viewtopic.php?t=51221 Using XIRR in Excel], forum discussion</ref> See the Excel help file to understand how it works.

==See also==
*[[Bond pricing]]
*[[Bond yield]]
*[[Comparing CDs]]
*[[Calculating personal returns]]
*[[Using a spreadsheet to maintain a portfolio]]

==References==
{{Reflist|30em}}

==External links==
*[http://www.investopedia.com/articles/03/082703.asp Understanding The Time Value Of Money]
*Fabozzi, Frank J., "Fixed Income Mathematics, Analytical & Statistical Techniques", [http://www.amazon.com/Fixed-Income-Mathematics-Frank-Fabozzi/dp/0786311215?tag=bogleheads.org-20 3rd edition]. Also available:  [http://www.amazon.com/Fixed-Income-Mathematics-Analytical-Statistical/dp/007146073X?tag=bogleheads.org-20 4th edition]
*Files used for this article (download from Google Docs): [http://docs.google.com/leaf?id=0B8VSNcUR-B6IMzYxZTUyMWYtMzdmZS00YzE2LWExYTEtNTk2NDAwMmU3NDJm Comparing Investments.zip]
**No account required. Contains MS PowerPoint, MS Excel in Office 2003 compatible format.
*[http://en.wikipedia.org/wiki/Net_present_value Net present value], on Wikipedia
*[http://en.wikipedia.org/wiki/Internal_rate_of_return Internal rate of return]. on Wikipedia
*[http://www.tvmcalcs.com/ Time Value of Money and Financial Calculator Tutorials] - Time Value of Money using Microsoft Excel and financial calculators. Example files are available for download.
**[http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page1 Microsoft Excel as a Financial Calculator Part I]
**[http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization Loan Amortization with Microsoft Excel]

===Cash flow diagrams===
*[http://h10032.www1.hp.com/ctg/Manual/c00363319.pdf HP-12C Financial Calculator User's Guide], from [http://www.hp.com/#Product Hewlett-Packard]
*[http://www.frickcpa.com/tvom/TVOM_CashFlowDiagram.asp Time Value of Money Concepts], from [http://www.frickcpa.com/tvom/default.asp Time Value of Money Concepts]

===Excel functions===
Microsoft's help files are well documented with working examples for each function. If using Open Office, Microsoft descriptions may be more informative. Enable browser cookies to view.
*[https://support.office.com/en-us/article/PV-function-23879d31-0e02-4321-be01-da16e8168cbd PV], Present Value.
*[https://support.office.com/en-US/article/NPV-function-8672CB67-2576-4D07-B67B-AC28ACF2A568 NPV], Net Present Value
*[https://support.office.com/en-US/article/IRR-function-64925EAA-9988-495B-B290-3AD0C163C1BC IRR], Internal Rate of Return
*[https://support.office.com/en-US/article/XIRR-function-DE1242EC-6477-445B-B11B-A303AD9ADC9D XIRR], Internal Rate of return for cash flows that are ''not'' periodic
*[https://support.office.com/en-US/article/EFFECT-function-910D4E4C-79E2-4009-95E6-507E04F11BC4 Effect], Convert to Effective annual yield from the periodic rate
*[https://support.office.com/en-US/article/NOMINAL-function-7F1AE29B-6B92-435E-B950-AD8B190DDD2B Nominal], Convert to Periodic interest rate from the effective annual yield
*[https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE RATE], Interest rate per period of an annuity

===Forum discussions===
*[http://www.bogleheads.org/forum/viewtopic.php?p=666701 $140,000 over 3 years or $160,000 over 5 years]
*[http://www.bogleheads.org/forum/viewtopic.php?p=686869 New in Wiki - Comparing Investments], forum discussion
*[http://www.bogleheads.org/forum/viewtopic.php?t=51221 Using XIRR in Excel], forum discussion

{{Bogleheads investing start-up kit|state=uncollapsed}}
{{Financial theory}}
[[Category:Financial theory]]