Question

2) Water damage from a major flood in a Midwestern city resulted in damages estimated at $108 million. As a result of the claimant payouts, insurance companies raised homeowners' insurance rates by an average of $59 per year for each of the 160,000 households in the affected city. If a 20-year study period is considered, what was the rate of return on the $108 million paid by the insurance companies?

a) [hand calculation]

b) [Use MS Excel]

Answer 1

By Hand

Annual increase in premium = 160000 * 59 = 9440000

Let ROR be i%, then

9440000 * (P/A,i%,20) = 108000000

(P/A,i%,20) = 108000000 / 9440000 = 11.440677

using trail and error method

When i = 6%, value of (P/A,i%,20) = 11.469921

When i = 7%, value of (P/A,i%,20) = 10.594014

using interpolation

i = 6% + (11.469921-11.440677) /(11.469921-10.594014)*(7%-6%)

i = 6% + 0.033% = 6.03%

Using Excel

Homes	160000
Insurance premium increase	59
Annual premium	9440000
Payout	108000000
t	20
ROR	6.03%

Showing formula in excel

Homes	160000
Insurance premium increase	59
Annual premium	=L9*L8
Payout	108000000
t	20
ROR	=RATE(L12,L10,-L11)