Comparing investments

This article 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.

Caveat: 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, Open Office Calc will work equally well and is a free application that is supported on several operating systems. Open Office financial functions are very similar to Microsoft's functions.

To use this article, copy the formulas from the example tables below and paste into a spreadsheet. The examples are also available for download at the bottom of 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 :
 * PV = FV * 1 / (1 + i)N or PV = FV * (1 + i)-N
 * 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).


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


 * +Equate Future Prices to the Present
 * Reference future values to present value.png
 * }

Fundamental Property #1
The first important property of present value is that the higher the interest rate (or discount rate ), the lower the present price.

The higher the interest rate today, the less that has to be invested 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.
 * Increases in the interest rate lowers the price.png
 * }

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.

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.
 * Longer investement time - lower price.png
 * }

How to Compare Investments

 * 1) Sum the individual cash flows (every payment and any final value) in today's price for each investment.
 * 2) Compare the prices.

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.
 * Compare offered price to present value.png
 * }

Example 1
An investing opportunity offers to pay $500 per year for the next 20 years. The purchase price of this opportunity is $5,300.

If the investor wants a 5.5% interest rate, is this a good investment?

The present value (price today of the investment 20 years later) is $5,975.19.

Since the present value is higher than the purchase price of $5,300, you will be getting more income than desired. This is a good investment.

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"|$5,975.19
 * align = "center"|=PV(5.5%,20,-500)
 * }
 * }

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?

The present value (price today of the investment 5 years later) is $1,156.90.

Since the present value is lower than the purchase price of $1,243.83, you will be paying more than what this investment is worth. Investment is not recommended.

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"|Cell
 * align = "right"|$418.39
 * =PV(6.25%, 5,-100)
 * B8
 * align = "right"|$738.51
 * =PV(6.25%,5,0,-1000)
 * B9
 * align = "right"|$1,156.90
 * =SUM(B8:B9)
 * B10
 * }
 * =SUM(B8:B9)
 * B10
 * }

A single payment at maturity is the Future Value (FV) parameter in the PV formula. There are no payments, which is set to zero as shown above.

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.

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.

With 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 
 * }
 * $0.00 
 * }

Excel formulas (copy these into your spreadsheet):


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


 * align = "center"|
 * align = "center"|Rate (Column C)
 * align = "center"|PV (Column D)
 * align = "center"|Purchase Price (Column E)
 * align = "center"|Difference (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
 * }
 * =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.
 * Compare two investments.png
 * }

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?

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 Annual Yield
 * align = "center"|Nominal Interest Rate
 * align = "center"|Monthly 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"|
 * align = "center"|Effective Annual Yield (Column K)
 * align = "center"|Nominal Interest Rate (Column L)
 * align = "center"|Monthly Interest Rate (Column M)
 * align = "center"|36 Month Investment (Column N)
 * align = "center"|60 Month Investment (Column O)
 * align = "center"|Difference (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. 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. Excels Nominal function "un-compounds" the annual rate into an equivalent monthly rate that can be correctly used in the PV formula. The Goal Seek function was used to find the annual interest rate, which flows into the monthly rate.

Compounded Interest Rates
As shown in Example 4, the time period and type of interest (compounded or simple) needs to be clearly defined. Use Excel to convert between an annual interest rate (yield) and the rate for the period of interest.

Example 5
Show the monthly interest rates for a given annual interest rate. Conversely, show the effective annual interest rate 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;"


 * align = "center" |Annual Yield (Column K)
 * align = "center"|Nominal Monthly Yield
 * align = "center"|
 * align = "center" |Monthly Yield (Column K)
 * align = "center"|Effective Annual Yield
 * align = "center"|10%
 * 9.57%
 * align = "center"|10%
 * 10.47%
 * align = "center"|8%
 * 7.72%
 * align = "center"|8%
 * 8.30%
 * align = "center"|6%
 * 5.84%
 * align = "center"|6%
 * 6.17%
 * align = "center"|4%
 * 3.93%
 * align = "center"|4%
 * 4.07%
 * align = "center"|2%
 * 1.98%
 * align = "center"|2%
 * 2.02%
 * }
 * align = "center"|4%
 * 4.07%
 * align = "center"|2%
 * 1.98%
 * align = "center"|2%
 * 2.02%
 * }
 * align = "center"|2%
 * 2.02%
 * }

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


 * width="10%"|
 * align = "center"|Annual Yield (Column K)
 * align = "center"|Nominal Monthly Yield
 * align = "center"|
 * align = "center"|Monthly Yield (Column N)
 * align = "center"|Effective Annual Yield
 * align = "center"|Row 22
 * align = "right"|0.1
 * align = "center"|=NOMINAL(K22,12)
 * align = "right"|0.1
 * =EFFECT(N22,12)
 * align = "center"|Row 23
 * align = "right"|0.08
 * align = "center"|=NOMINAL(K23,12)
 * align = "right"|0.08
 * =EFFECT(N23,12)
 * align = "center"|Row 24
 * align = "right"|0.06
 * align = "center"|=NOMINAL(K24,12)
 * align = "right"|0.06
 * =EFFECT(N24,12)
 * align = "center"|Row 25
 * align = "right"|0.04
 * align = "center"|=NOMINAL(K25,12)
 * align = "right"|0.04
 * =EFFECT(N25,12)
 * align = "center"|Row 26
 * align = "right"|0.02
 * align = "center"|=NOMINAL(K26,12)
 * align = "right"|0.02
 * =EFFECT(N26,12)
 * }
 * align = "right"|0.04
 * =EFFECT(N25,12)
 * align = "center"|Row 26
 * align = "right"|0.02
 * align = "center"|=NOMINAL(K26,12)
 * align = "right"|0.02
 * =EFFECT(N26,12)
 * }
 * =EFFECT(N26,12)
 * }

See the Microsoft help files below for more information. Enable browser cookies to view.
 * Nominal, Microsoft help file for Nominal interest
 * Effect, Microsoft help file for Effective annual rate

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
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.

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" |
 * align = "center" |Interest Rate (Quarterly) (Column C)
 * align = "center" |3 Month Period (Column D)
 * align = "center" |Payment (Column E)
 * align = "center" |Present Value (Column F)
 * align = "center" |Net Present Value (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"|
 * align = "center"|Row 22
 * align = "center"|0.03
 * align = "center"|3
 * align = "center"|1500
 * align = "right"|$1,372.71
 * align = "right"|
 * align = "center"|Row 23
 * align = "center"|0.03
 * align = "center"|4
 * align = "center"|1700
 * align = "right"|$1,510.43
 * align = "right"|
 * align = "center"|Row 24
 * align = "center"|0.03
 * align = "center"|5
 * align = "center"|1800
 * align = "right"|$1,552.70
 * align = "right"|
 * align = "center"|Row 25
 * align = "center"|0.03
 * align = "center"|6
 * align = "center"|2000
 * align = "right"|$1,674.97
 * align = "right"|
 * align = "right"|Sum of Present Value:
 * align = "right"|$8,212.79
 * align = "right"|
 * }
 * align = "right"|$1,674.97
 * align = "right"|
 * align = "right"|Sum of Present Value:
 * align = "right"|$8,212.79
 * align = "right"|
 * }
 * align = "right"|Sum of Present Value:
 * align = "right"|$8,212.79
 * align = "right"|
 * }

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


 * align = "center" |
 * align = "center" |Interest Rate (Quarterly) (Column C)
 * align = "center" |3 Month Period (Column D)
 * align = "center" |Payment (Column E)
 * align = "center" |Present Value (Column F)
 * align = "center" |Net Present Value (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"|
 * align = "center"|Row 22
 * align = "center"|0.03
 * align = "center"|3
 * align = "center"|1500
 * align = "center"|=PV(C22,D22,0,-E22)
 * align = "center"|
 * align = "center"|Row 23
 * align = "center"|0.03
 * align = "center"|4
 * align = "center"|1700
 * align = "center"|=PV(C23,D23,0,-E23)
 * align = "center"|
 * align = "center"|Row 24
 * align = "center"|0.03
 * align = "center"|5
 * align = "center"|1800
 * align = "center"|=PV(C24,D24,0,-E24)
 * align = "center"|
 * align = "center"|Row 25
 * align = "center"|0.03
 * align = "center"|6
 * align = "center"|2000
 * align = "center"|=PV(C25,D25,0,-E25)
 * align = "center"|
 * align = "right"|Sum of Present Value:
 * align = "center"|=SUM(F20:F25)
 * align = "center"|
 * }
 * align = "center"|=PV(C25,D25,0,-E25)
 * align = "center"|
 * align = "right"|Sum of Present Value:
 * align = "center"|=SUM(F20:F25)
 * align = "center"|
 * }
 * align = "right"|Sum of Present Value:
 * align = "center"|=SUM(F20:F25)
 * align = "center"|
 * }

Refer to Microsoft's help file on NPV for additional information and another example. Enable browser cookies to view.
 * NPV, Microsoft help file for Net Present Value
 * PV, Microsoft help file for Present Value.

Both Present Value and Net Present Value assume that the interest rate is defined. Suppose that you only have the payments. How do you calculate the interest?

Not a problem. Just use the Internal Rate of Return function, 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.
 * IRR, Microsoft help file for Internal Rate of Return
 * Enable browser cookies to view

Forum Discussions

 * $140,000 over 3 years or $160,000 over 5 years