Please show me the RATE formula

2) a. (25 points) Assume that you are looking to buy bonds with a face value of $1,000 and a 6.5% coupon rate (coupons paid semi-annually). Create a table in Excel showing the annual yield to maturity (YTM) of these bonds for different prices of the bonds from $800 to $1200 (using $50 intervals) and three different lengths of time to maturity: 5 year, 10 year, and 30 year. Use the RATE function that we covered in class and the right mix of absolute and relative cell references. (NOTE: You should only have to write the formula for one cell and then copy and paste for the rest of the cells in your price/maturity matrix. Also, remember to have no hard coding at all in your formulas: all elements of the RATE function should be absolute or relative cell references). A B C D E F G H 1 2 3 $ 800.00 $ 850.00 $ 900.00 $ 950.00 $ 1,000.00 $ 1,050.00 $ 1,100.00 $ 1,150.00 $ 1,200.00 5 5 year bond 10 year bond 30 year bond 6 7 8 9 10 11 Coupon rate Face Value Coupons per year 6.50% $ 1,000.00 2