Spreadsheets and rate of return analysis
Rate of Return Analysis
Spreadsheets and rate of return analysis
Go to questions covering topic below
When the equations are simple such as at most one P and one A and/or one F then the RATE function may be used to determine ROR.
Example 1:
Here is an example based on Example 7-1from the text. What is the ROR?
| P | A | n |
| -8200 | 2000 | 5 |
Paste this table into an empty spreadsheet starting at A1. Then paste: “=Rate(C2,B2,A2)” into A3; it will show the ROR = 7%.
Example 2:
Now consider this data:
| P | A | G | n |
| -700 | 100 | 75 | 4 |
RATE does not provide for the arithmetic gradient, G. However, if we list the set of annual cash flows, the IFF function may be used. Again, from the text, the above data becomes:
| A | B | |
| 1 | Year | Cash flow |
| 2 | 0 | -700 |
| 3 | 1 | 100 |
| 4 | 2 | 175 |
| 5 | 3 | 250 |
| 6 | 4 | 325 |
| 7 |
In an empty spreadsheet paste the data part of the above table into the cells indicated. Then type “=IRR(B2:B6)” into cell A7 and the cell will show 6.91% or rounded to 7%. (You may have to adjust the number of decimals shown.)
Rate of Return Analysis
Spreadsheets and rate of return analysis
Return to Spreadsheets and rate of return analysis
Return to Rate of Return Analysis Tutorials menu
Question 1.
Consider the following data:
The initial price of an asset is $45,000 and it is expected to earn $12,000 each year for five years. What is its ROR?
Choose an answer by clicking on one of the letters below, or click on "Review topic" if needed.
A 2.63%
B 10.42%
C 15.34%
D -14.4%
Question 2
Here are the data for an asset that is being considered:
Initial cost = $35,000
Salvage value at 5 years = $3000
Rebuild cost at 3 years = $23,000
Annual net cash flow = $22,000 per year
What is the ROR for this asset?
Choose an answer by clicking on one of the letters below, or click on "Review topic" if needed.
A 57.0%
B 41.0%
C 43.9%
D 42.5%
