Can you help me fill the table? i dont really understand. i can pay you extra if you can send me back a filled excel and everything detailed and all annotated.

XYZ Insurance Co wants to buy a block of structured settlement annuities.

The expected cash flows of the block have been calculated by the actuary of the selling company. They are the following:

Liab

Year Cash Flows

$12\mathrm{.}000\mathrm{.}000$

$22\mathrm{.}000\mathrm{.}000$

$32\mathrm{.}000\mathrm{.}000$

$4125\mathrm{.}000\mathrm{.}000$

$55\mathrm{.}000\mathrm{.}000$

$65\mathrm{.}000\mathrm{.}000$

$7100\mathrm{.}000\mathrm{.}000$

$84\mathrm{.}000\mathrm{.}000$

$920\mathrm{.}000\mathrm{.}000$

$1060\mathrm{.}000\mathrm{.}000$

$1180\mathrm{.}000\mathrm{.}000$

$125\mathrm{.}000\mathrm{.}000$

$135\mathrm{.}000\mathrm{.}000$

$145\mathrm{.}000\mathrm{.}000$

$1510\mathrm{.}000\mathrm{.}000$

$165\mathrm{.}000\mathrm{.}000$

$175\mathrm{.}000\mathrm{.}000$

$185\mathrm{.}000\mathrm{.}000$

$194\mathrm{.}498\mathrm{.}685$

$20100\mathrm{.}000\mathrm{.}000$

This block is supported by a portfolio of three assets:

$5-$Year Bond $-5\%$ coupon $-40,000,000$

$10-$Year Bond $-5\%$ coupon $-60,000,000$

$20-$Year Bond $-5\%$ coupon $-245,000,000$

For this analysis, you will work only with annual cash flows. Therefore, you will assume that the coupons on the bonds are paid annually at the end of the year and also that the liability cash flows occur at the end of the year.

Also, you will assume the market rate on all assets is $5\%.$

The task in this exercise is to project the net cash flow from the assets and liabilities. At the end of each period, the company have to either fund this cash flow $($if it is negative$)$ or re$-$invest the cash if it is positive. If the cash flow is negative, it will sell assets $($shortest assets first$)$ to cover the negative balance. If it is positive, it will re$-$invest the excess in $1-$year bonds paying the current market rate. Remember, when it sells an asset, its market value is the present value of future cash flows discounted at the current market rate. Other points to note:

$$ The market value of an asset is based on the interest rate in that period.

$$ The one$-$year bond gets reinvested at the rate available in the year it is purchased.

$$ If a portion of an asset is sold, its remaining face will decrease proportionate to the amount of market value sold.

COMPLETE THE FOLLOWING TABLE: