Using Graphs in MS Excel to Avoid Heavy Calculations

I got the following question in my ‘finance’ exam and the answer 11.79 (%) was marked wrong. Can you help me? Queen Fisher Airlines has been granted permission to fly passengers between major cities of UK. There is a huge competition with four other airlines operating in the same sector. The betas of the equity of the four major competitors (P, Q, R, S) are 2.25, 2.50, 2.75, and 3.00 and their debt-to-equity ratios (D/E) are 0.21, 0.42, 0.63, 0.83 respectively. The D/E ratios of all the competitors vary but we can treat the debt of all airlines as being same. The interest rate on debt is 7% and the risk-free rate is 3% and the expected market risk premium which is defined as the average difference between the market return and the risk-free rate, is 5%. How would you determine the cost of capital that could be used to value Queen Fisher airlines.


Why do you need to determine the cost of capital using data of comparable firms? Because that is the correct way of figuring out the ‘market’ or real cost of capital. Once that is determined you can find out whether your cash-flow forecasts will create value for your customer. Based on this data you can convince investors to invest in your company or get loans from banks.
From the data provoded by the question you can easily calculate the Rate of return on Equity using the formula:

Return on Equity (Re) = Risk Free Rate (Rf) + Market Risk Premium (Rm – Rf) * Beta of Equity

Once the Re has been calculated you can use the equation below to calculate the Return on Asset (Ra):

Re = Ra + D/E [Ra – Rd]

Re: we just calculated it; D/E is given; Rd is given at 7%

However the process of calculation is tedious and error prone. Also you need to format the Ra values appropriately to work faster.

You can make the calculations relatively simpler and quicker if you plot the given values of the Beta of Equities and the D/E ratios. Extending the graph appropriately gives you the Beta of Equity at D/E=0. Now the equation
Re = Ra + D/E [Ra – Rd]
Re = Ra
Using the above formula
Re = Rf + (Rm-Rf) * Beta of equity, we get Ra immediately.

The picture below also depicts the solution:

Using graphs in Excel to avoid calculations

Using graphs in Excel to avoid calculations

Watch the video below to learn how to use graphs in MS Excel to avoid heavy calculations:

Watch the Video on YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *