A bond portfolio named VEX, comprises four bonds $($face value$=$$$1000)$:

$1)100$ semi$-$annual bond, $5-$year maturity, a coupon rate of $4\%$

$2)200$ annual bonds, $30-$year maturity, $8\%$ coupon bond.

$3)300$ zero coupon bonds, $10-$year maturity.

$4)400$ zero coupon bonds, $20-$year maturity.

If required yield $($discount rate$)$ is $6\%$ for all bonds per year, what is the market fair value of VEX? What is each bond$$s weight in the portfolio? Please only use excel and show formulas in excel!!!!!