John has $300,000 in riskless assets, which are not subject to a loss, and a house worth $500,000. His house faces a 1% chance of burning down, resulting in total loss of value. He can purchase insurance on his house, and the insurer charges a 25% loading on top of the actuarially fair premium. John's utility function of final wealth is given by u(w)=2w + 10.

Solve the question by using the Excel

Find the optimal level of coverage. In the situation described above, what percentage of the loss should John optimally insure? Use the following steps to find the answer:

• Create cells for the following inputs: "Assets", "House Value", "Loss Probability", "Loading." Put in the values from the problem
• Create column"Level of Coverage". Start at 0% and increase the level of coverage to 100% in increments of one percentage point. (That is, go from 0%, to 1%, to 2%, etc., all the way up to 100% coverage.)
• Create column"Premium"to determine the premium charged by the insurer for each possible level of coverage. Determine the actuarially fair premium for each coverage level and then apply the loading.
• Create columns"Wealth in the No-Loss State"and"Wealth in the Loss State"to find John's final wealth levels for each level of coverage.
• Create column"Expected Utility"to determine John's expected utility for each level of coverage.
• Inspect the last column to find the level of coverage thatmaximizesJohn's expected utility. Think about a clever way to find this maximum.
• Please show the Excel file with all the answer.