RATE OF RETURN WORKSHEETS
One problem when comparing different investments using rate-of-return analysis is the probability of achieving the projected result.
For instance, a single-project research and development partnership could show a 38% internal rate of return, but the odds of success are one-in-nine. Buying "up and built" multi-family apartment buildings may show a 14% internal rate of return, but the odds of success are almost one out of one. Quantitative return calculations need to reflect the probability of success.
Both worksheets below are used to show you how to calculate an investment's rate of return, and blank forms are provided to enter the data you need. While some may feel using a calculator is "old-school", we feel like it's the simplest method for those trying to "do this at home", so we have provided easy, step-by-step keystrokes for using a Hewlett Packard Financial Calculator to generate the results that are entered in the accompanying worksheets. And electronic spreadsheets are available with our paid consulting services packages.
Either way, we will step through both Adjusted Rate of Return and Internal Rate of Return calculations so you are able to compare investments using both tools.
Getting started:
We recommend the HP12CP Financial Calculator, and the price varies based on the source, $50.00 - $99.00 at Amazon, Office Depot et. al. Note: the "classic" HP12C is still available, and while it will be fine if you already have one, the newer HP12CP ("P" = Platinum Edition) like most other electronics, is faster, has more memory, etc.; and in some cases, is even less expensive. You might also choose to purchase a HP12C app for your mobile phone.
Download and print Worksheet 1: Present Value Worksheet
Download and print the Worksheet 2: Economic Benefits Worksheet
Next Steps:
1 - Determine the Present Value
2 - Determine the Economic Benefits
3 - Calculate the Adjusted Future Value of Economic Benefits
4 - Calculate the Adjusted Rate of Return
5 - Calculate the Internal Rate of Return
Step 1: Determine the Present Value
1. Use Worksheet 1: Present Value Your initial investment is a current cash outlay, so the amount of the investment is also the present value amount. Enter this number in the first line under Cash Paid, as well as under the "Present Value of Investment" column on the form above.
2. List on the form each subsequent investment pay-in, if any, in the period in which it occurs.
3. Enter into the calculator the amount of the second investment pay-in (if any). Press the "CHS" (Change Sign) button; then press "FV" (Future Value) button.
4. Enter the number of the period in which this investment is paid; then press the "n" (Number of Periods) button.
5. Enter the discount rate (the safe rate); then press the "i" (Percentage Interest Rate) button.
6. Press the "PV" (Present Value) button which will give you the present value of the second pay-in. Enter the answer in the second line under the Present Value Column.
7. Repeat Steps 3 through 6 for each investment pay-in and then add them up. The total is the present value of the investment.
Step 2: Determine the Economic Benefits
1. Use Worksheet 2: Economic Benefits List the investment's projected Taxable Loss/(Income) for each year, then multiply by your assumed tax rate. The result is the amount of Tax Savings per year.
2. Add the investment's projected Tax Savings, Cash Flow and After-Tax Sales Proceeds for each year. The sum is the investment's Total Economic Benefits per year. Enter these amounts in the first several columns in the worksheet. For real estate investments the After-Tax Sales Proceeds are calculated as follows: Cash from Sale of Property plus Cumulative Tax Loss and Cumulative Cash Flow less Cash Investment equals Taxable Gain. Calculate Taxes Due on Sale. Subtract Taxes Due on Sale from Cash from Sale of Property to find After-Tax Sales Proceeds.
Step 3: Calculate the Adjusted Future Value of Economic Benefits
1. Continue using the Economic Benefits Worksheet 2 as per the above, enter the amount of the first year's Economic Benefit; press "CHS" button; then press "PV" button.
2. Enter the safe rate, then press "i";
3. Enter the Reinvestment Period, then press "n". The Reinvestment Period is the number of years from receipt of the Economic Benefit until the sale of the investment. If you are estimating the sale of an investment seven years after the first pay-in of original investment, the Reinvestment Period will be six years for an Economic Benefit received in the second period. The second period can be the same year as the year of original investment.
4. Press the "FV" button and the calculator will then display the Future Value of the first economic benefit, which takes into account the earnings of reinvesting the benefit at the "safe" rate until the investment is sold. This is the Adjusted Future Value of Economic Benefits.
5. Calculate the Future Value of each year's Economic Benefit by following Steps 1 through 4, and then add them up. The total is the Adjusted Future Value of Economic Benefits for this investment.
Step 4: Calculate the Adjusted Rate of Return
1. On the HP12CP, enter the total Present Value of Investment from Step One above, press the "CHS" button; then press the "PV" button.
2. Enter the number of years the investment was held; then press the "n" button.
3. Enter the total Adjusted Future Value of Economic Benefits; then press the "FV" button.
4. Press the "i" button. The Adjusted Rate of Return will then be displayed, representing the compound rate of return on your investment assuming reinvestment of benefits at the "safe" rate.
Step 5: Calculate the Internal Rate of Return
1. Enter the total Present Value of Investment from Step One; then press (in order) the buttons, "CHS" and "g CFO".
2. Enter the first yearly Economic Benefit; then press the buttons "g" and "CFo".
3. Repeat Step Two for each Economic Benefit in order from the first one received to the last.
4. Press f, then IRR. The IRR will then be displayed (depending on the model this make take up to several minutes).