How would I construct the excel spreadsheet for this question? The algebraic solution is below.

A small airline recently sold to a private equity group for $145 million. The airline has earned profits of $9 million last year. The new managers believe they can grow profits at 5% per year. The private equity group borrows money from wealthy individuals to invest in acquisitions. Because of the significant risk involved, lenders are promised a 12% return on their loans to the equity group. Is the purchase price of the new airline reasonable?

For the private equity, the cost price of the airline will be $145mn, i.e. cash outflow.

The cash inflow will be the earnings of the airline each year. So the earnings of the airline for the previous year were $9mn.

Their earnings in year 1 = 9*(1+0.05)= 9.45

Their earnings in year 2 = 9*(1+0.05)*(1+0.05)

Their earnings in year 3 = 9*(1+0.05)*(1+0.05)*(1+0.05) and so on...

The discounted earnings for each year will be the nominal earnings discounted to year 0 by 12% each year.

So,

Their discounted earnings in year 1 = 9*(1+0.05)/(1+0.12)

Their discounted earnings in year 2 = 9*(1+0.05)*(1+0.05)/(1+0.12)/(1+0.12)

Their discounted earnings in year 3 = 9*(1+0.05)*(1+0.05)*(1+0.05)/(1+0.12)/(1+0.12)/(1+0.12)

Please pay attention that the earnings are discounted to year 0. Hence the sum of all the inflows is 9.45 + 9.45(1.05)/1.12 + 9.45(1.05)(1.05)/(1.12)/(1.12)+... till infinity this is a geometric progression (where a=9.45 and r = (1.05/1.12) whose sum is as follows

Sum= 9.45/(1-(1.05/1.12)) = 144

Hence the valuation of the airline stands at $144mn

Now, it was sold to the private equity group for $145mn, which is more than its actual valuation.

Hence it is not prudent for the group to buy the airline. The sale was not reasonable!