# 14|EXCEL – Bond Fun(d)

BYU Student Author: @Mark_Albrechtsen
Reviewers: @Parker_Sherwood, @Boston
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

• Excel

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.

Data Files

Suggestions and Hints

The PV formula should be used.
Check Figure: Bond Type 3 has a current price of \$1,013.41.

Solution

Here is my solution

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!

1 Like

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.

Here’s my solution screenshot:

3 Likes

I did the same thing as you @Jonathan_Weston. But I put it into an ABS() formula because it helps my brain process it a little better!

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!

1 Like

Here’s my solution!
Challenge14_Bond_Fund.xlsx (10.2 KB)

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!

IMPORTANT FROM AUTHOR: This challenge has been fixed. The solution files, image, and videos are correct.

1 Like

Also got similar solutions to what others posted.

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:

Solid refresher on basic financial formulas. Thanks for the challenge!

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!

Challenge14_Bond_Fun(d).xlsx (10.2 KB)
Great practice to remind myself how to value bonds!

here is my solution
Challenge14_Bond_Fun(d).Solution .xlsx (10.1 KB)

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)

Here’s my solution! Thanks for you the review on bonds!

1 Like

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:

`=-PV(B7/B5,B6*B5,B3*B4/B5,B3)`

Here is a picture of my finished solution:

This challenge is useful to my field of accounting because I use financial formulas on a daily basis.

3 Likes

This was a great challenge! I appreciate the reminder of the PV function in Excel. The formula I used for determining price was:

=-PV(B7/B5,B6 * B5,(B4 * B3)/B5,B3)

Thanks for this challenge!

Challenge14_Bond_Fun(d).xlsx (10.2 KB)

This challenge was a great refresher on the nature of bonds and how they work!