Overview
You are currently an intern at an investment company that manages different types of funds, or pooled investments, including mortgage-backed securities and mutual funds. Your team is creating a bond fund, which is essentially a pool of individual bonds combined into one large investment. The purpose of such funds is to provide fixed income in the form of coupon payments while diversifying away the risk of owning individual bonds. The funds are divided up into equal parts (“fund shares”) and sold to investors. Your team has decided on four different bond types and their quantities to include in their particular fund. Your team has also decided to sell 24,500 fund shares and has tasked you with determining the current present value of the soon-to-be-issued shares.
Instructions
Find the present value of each bond type within the portfolio. Then, use these values to determine the present value of each fund share.
Looks like my answers differ from yours a bit. Looks like I multiplied the years until maturity by the number of payments per year to get my nper in my present value calculation. While you kept the nper the same as the years until maturity, but divided the coupon payment by the number of payments per period. Both of us divided the annual rate by the number of payments per period though, so that’s good.
Would like someone to explain why my reasoning is off, thanks!
My answers were also different from the solution file. Just how the Nper argument is “Years until Maturity” * " Payments per Year", the PMT and Rate arguments also need to be divided by “Payments per Year”. A good check is if your coupon rate is lower than your Market rate, the bond will sell at a discounted price, and visa versa with premium bonds.
ALL: This challenge’s solution is incorrect. @Mike_Paulsin 's solution is correct based on my intentions. I had decided to use a coupon rate that was not annual (ie, each coupon payment is the face value multiplied by the coupon rate on the sheet) for simplicity’s sake. I made a mistake in not multiplying the number of payments per year by the years to maturity.
I will revisit this during class tomorrow and fix it. I will probably end up using an annual coupon rate as @Jonathan_Weston and @IWillyerd have assumed. Thanks for reviewing this! Nice job finding my mistake!
I have the same answer as the others, and I know you said you’re going to update the challenge, but it was still a nice refresher on how to use present value in excel. I’m so used to my financial calculator that I forget how to do it excel. Thanks for the reminder!
I had fun filling out this challenge! To make it a little more of a challenge I made one dynamic formula for all of the bond types. Having the various payments per year made for an interesting adjustment!
What a good problem on Present value calculations! I almost missed the payments per year in thinking through what numbers I was including, but when I saw it changed, I remembered there are a few corrections to make when multiple payments are made each year. I love the practice! Here were my formulas in each box with B17 copied as a hard coded value into B18:
Found this really great to help dust off some cobwebs on general thinking about bonds, especially away from a calculator. Great beginning stretch starting the school year. Thanks for the challenge!
I was not previously aware of how to calculate TVM problems using excel, so this was very helpful for learning those functions. Challenge14_Bond_Fun(d).xlsx (10.2 KB)
The formula builder is such a great tool, especially for solving the time value of money and finding the present value of funds. I will definitely utilize it more in the future!
Here is the formula I used to calculate the current price of the bonds:
