2) 2340.125 3) A/B) -5%: 39.09 0%: 137.24 5%: 245.18 10%: 363.64 15%: 493.37 4) Price: 715.00 Duration: 7.16 Convexity: 63.07 Dispersion: 11.84 5) Spot: 7.42 Forward: 7.595 MTH 491B Excel Project Problem 1 Recreate the following bond amortization schedule: The bond is a $100 par, 5-year bond with semiannual coupons paid at 4% per year earning an annual effective yield of 9%. You need to complete EVERYTHING about the table, including number and text formats (decimal places and centering), borders, and titles. Some relationships that may be useful: = = = 1+ = + 1+ Remember that is the amount of interest earned in the t-th payment is the premium amortized in the t-th payment, which is the price difference between two periods (see the formula above). Premium Amortized is the same as Accumulated Discount, but the interpretation is different. If the bond sells at a premium (meaning the price is higher than the redemption value), then your bond price will decrease over the lifespan, hence premium amortized. If the bond sells at a discount (meaning the price is less than the redemption value), then your bond price will increase over the lifespan, hence accumulated discount. Problem 2 Calculate the present value of a 5-year increasing quarterly payable annuity immediate that has an initial payment of $50 and has an annual effective interest rate of 8%. (That is, the bond pays $50-$50-$50-$50 for the first 4 quarters, then $100-$100-$100$100...) You should be doing this as a row or column calculation in Excel, but you may use a formula to check your final answer. Problem 3 An investment has an initial cost of $1000 at time 0. The cash flows in the future are paid at the end of each year starting with $300 at time 1 and increase by 5% each year until the last payment at time 5. Assume the interest rate on the investment is 10%. A) Calculate the net present value of the investment. B) Calculate the net present value of the investment for different values of the compound increasing factor: -5%, 0%, 5%, 10%, 15% C) Is the net present value an increasing, decreasing, or monotone function of the compound increasing factor? Problem 4 Calculate the Macaulay duration, Macaulay convexity, and dispersion of a 10-year bond with semiannual coupons paid at 6% per year earning an annual effective yield of 11%. Problem 5 Given the following spot rates and forward rates (in percents) for the effective years: Rate/Year 1 2 3 4 5 6 7 8 9 10 Spot 5 6 7 7 6 8 9 6 4 3 Forward 2 7 6 5 4 8 8 9 4 2 Find the present value factor of an annuity immediate under both schemes. (These figures may not match because the rates aren't designed to match.) What you will be turning in 1) When you are done with the problems, let me know and I will come around and look at your spreadsheet. If it is satisfactory, I will mark it off and you may move on to Project 2. Don't be afraid to hand calculate some of these numbers. This is a good way to verify that what you are doing formulaically and spreadsheet-ily are both, at least, in agreement. This will help you understand how to use your formulas better because you will be much more comfortable understanding just exactly what and how the formula is calculating. One last word of wisdom is that if you choose to use excel functions rather than brute forcing some of the calculations, make sure you understand just exactly how excel calculates these figures; they may be different than you expect. How you will be graded You will be graded on the cleanliness, accuracy, and efficiency of your submission. 1) Formatting of tables and numbers in problem 1 will be important. 2) Problem 2 will be based on how well you've used excel formulas and cell references - for example, you shouldn't be entering payments of 50-50-5050-100-100... You should be able to come up with a formula based on the time at which the payment is made to determine the amount of the payment. 3) You should have a neat table for Problem 3. The explanation doesn't have to be in the table. 4) Similar to Problem 2. 5) For problem 5, you will likely have a table and should have a mildly clever use of cell referencing in your calculation. Good luck