Solve each of the following two problems using Microsof EXCEL. 1. Rate of Return

Question

Solve each of the following two problems using Microsof EXCEL.

1. Rate of Return Analysis: You have $23,000 in a bank savings account today. You wish to withdraw $3,400 per year for the next 25 years with the first withdrawal being one year from today. When you make the last withdrawal of $3,400 from your account 25 years from now the account will be empty and you will have withdrawn all your money. Compute the actual rate of return to within plus or minus 0.1% using Microsoft EXCEL.

2. Minimum Attractive Rate of Return: Use Present Worth analysis and determine which of the following two machines should be selected based on their incremental cash flows and a minimum attractive rate of return (MARR) of 11% per year. Compute the actual rate of return to within plus or minus 0.1% using Microsoft EXCEL.

Please show work and formulas used.

Item Machine A Machine B First cost, $ -43,000 -65,000 Annual operating cost, $/year -14,500 -9,000 Salvage value, $ 10,500 17,000 Life, years 5 10

Explanation / Answer

1) $23000 is the PV of the annuity of $3400 with number of years being 25.

Equating we have,

23000 = 3400*pvifa(r,25), where 'r' is the rate of return.

23000/3400 = pvifa(r,25)

6.7647 = pvifa(r,25)

The interest factor falls between 14% & 15%

14% - 6.8729

15% - 6.4641

Exact rate = 14 + (6.8729-6.7647)/(6.8729-6.4641) = 14.26%

2)

As the two machines have different lives, the selection should be based on Equvalent uniform annual cost Machine A Machine B PV of salvage value (10500/1.11^5) 6231 (17000/1.11^10) 5987 First cost -43000 -65000 Net first cost -36769 -59013 pvifa(11,5) 3.6959 pvifa(11,10) 5.8892 EUAC (excl. annual op costs) (Net first cost/pvifa) -9949 -10021 Add: Annual operating costs -14500 -9000 -24449 -19021 As the EUAC of Machine B is less, it should be selected.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.