Hi, can anyone provide me with the excel solution (with formulas) for this: Resi

Question

Hi, can anyone provide me with the excel solution (with formulas) for this:

Residual Income Model and Nonconstant Growth

AI's Infrared Sandwich company had a book value of %12.95 at the beginning of the year, and the earnings per share for the past year were $3.41. Molly Miller, a research analyst at Miller, Moore & Associates, estimates that the book value and earnings per share will grow at 12.5 and 11 percent per year for the next four years, respectively. After four years, the growth rate is expected to be 6 percent. Molly believes the required return for the company is 8.2 percent. What is the value per share for AI's Sandwich Company?

EPS(1 g)-B,Xk

Explanation / Answer

Forecast the book value and EPS given the growth rate

P4 = B4 + (EPS4 x (1 + g) - B4 x k) / (k - g)

= 20.74 + (5.18 x 1.06 - 20.74 x 8.2%) / (8.2% - 6%)

= $192.85

PV1 = (EPS1 + B1 - B0) / (1 + k)^1 = (3.79 + 14.57 - 12.95) / (1 + 8.2%)^1 = 4.99

PV2 = (4.2 + 16.39 - 14.57) / (1 + 8.25)^2 = 5.14 and so on..

P0 = 4.99 + 5.14 + 5.3 + 5.46 + 192.85 / (1 + 8.2%)^4

= $161.60

is the value per share today.

Year B EPS PV 0 $   12.95 $     3.41 1 $   14.57 $     3.79 4.994316 2 $   16.39 $     4.20 5.144299 3 $   18.44 $     4.66 5.298988 4 $   20.74 $     5.18 5.458537 5 $     5.49

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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