how would i do it on the spreadsheet.

e E - H 3 4 1/15/2024 1/15/2025 5 1/15/2026 1020 25.75 25.75 1010 25.5 25.50 950 25.25 975.25 1 TIPS vs Zero Coupon Bond 2 TIPS coupon rate (annual) 3 Time (year) 0 1 2 4 Dates (any starting date would work) 1/15/2021 1/15/2022 1/15/2023 5 Expected inflation 6 Notional principal (adjusted for inflation) 980 1030 7 Coupons 25 24.5 8 (a) Total periodic cash flows from TIPS -1000.00 25.00 24.50 9 10 (a) EAIR from the TIPS: 11 IRR 15596 12 13 XIRR 1 55% 14 15 (6) Required purchase price for a 5-year zero coupon bond: 16 Time (year) 0 1 2 17 Dates 1/15/2021 1/15/2022 1/15/2023 18 Cash flows -1000 ? 19 Rate 1.55% 20 Par value 1000 21 Coupon payment $15.50 22 # of periods 5 23 Required zero coupon bond price 24 25 3 1/15/2024 4 1/15/2025 ? 1/15/2026 1000+2 5. Suppose you want to invest your savings into a 2.5%, 5-year US Treasury Inflation Protected Security (TIPS) that makes coupon payments annually on the principal adjusted for inflation. Assume that the annual inflation rates over the next five years are expected to be -2%(deflation), 3%, 2%, 1%, and -5%(deflation) respectively. a) What would the annual cash flows to you from the TIPS be over the 5-year period? What effective annual rate of return (EAIR) would you earn on this investment? b) What price would you be willing to pay for a 5-year zero coupon Treasury Bond (not adjusted for inflation) that would make you indifferent between investing in the 5-year TIPS (in 'a') vs. investing in the zero-coupon bond? Assume that you trust the inflation forecast used in 'a', and that you intend to hold either of the bonds until maturity. e E - H 3 4 1/15/2024 1/15/2025 5 1/15/2026 1020 25.75 25.75 1010 25.5 25.50 950 25.25 975.25 1 TIPS vs Zero Coupon Bond 2 TIPS coupon rate (annual) 3 Time (year) 0 1 2 4 Dates (any starting date would work) 1/15/2021 1/15/2022 1/15/2023 5 Expected inflation 6 Notional principal (adjusted for inflation) 980 1030 7 Coupons 25 24.5 8 (a) Total periodic cash flows from TIPS -1000.00 25.00 24.50 9 10 (a) EAIR from the TIPS: 11 IRR 15596 12 13 XIRR 1 55% 14 15 (6) Required purchase price for a 5-year zero coupon bond: 16 Time (year) 0 1 2 17 Dates 1/15/2021 1/15/2022 1/15/2023 18 Cash flows -1000 ? 19 Rate 1.55% 20 Par value 1000 21 Coupon payment $15.50 22 # of periods 5 23 Required zero coupon bond price 24 25 3 1/15/2024 4 1/15/2025 ? 1/15/2026 1000+2 5. Suppose you want to invest your savings into a 2.5%, 5-year US Treasury Inflation Protected Security (TIPS) that makes coupon payments annually on the principal adjusted for inflation. Assume that the annual inflation rates over the next five years are expected to be -2%(deflation), 3%, 2%, 1%, and -5%(deflation) respectively. a) What would the annual cash flows to you from the TIPS be over the 5-year period? What effective annual rate of return (EAIR) would you earn on this investment? b) What price would you be willing to pay for a 5-year zero coupon Treasury Bond (not adjusted for inflation) that would make you indifferent between investing in the 5-year TIPS (in 'a') vs. investing in the zero-coupon bond? Assume that you trust the inflation forecast used in 'a', and that you intend to hold either of the bonds until maturity