I’m working on a project in Excel to see how much would need to be invested in Treasuries to meet annual spending needs for a company. I am trying to find the best way to account for semi-annual compounding in excel. For example: if the 2 Year Treasury is yielding 1.21%, how much would need to be invested to have $712,950 at the end of 2 years. I know how to figure this out, but I’m trying to find the best way to calculate this in excel. Thanks.
No need for fancy functions… I guess the following would do? 712950/[(1+1.21%/2)^4] replace the figures with cell references instead (i.e. FV, interest, years)
Thanks!
You could use the NPV function if you like, there’s a lot of ways to set it up. I wouldn’t hard code the numbers in your formula, rather if you don’t want to use a function you should have a formula pulling the inputs from an input reference area so that you can change assumptions easier.
I guess it would depend on whether these were coupon or zero coupon bonds. With zeros, you just have the final value and divide by the compounded interest rate. With coupon bonds, you can try to add up the expected coupons and par values to see if you get the right FV. If you use the YTM on a coupon bond, you will have a little bit of reinvestment risk… if the interest rate falls between now and the time you need the money, you won’t have quite enough from reinvesting the coupons. Should be pretty small difference though.