Question

A 10-year bond with a face value of $6000 is going to be purchased by you. Interest payments on the bond will be made to you by the bond issuer every quarter for ten years based on a nominal annual interest rate of 6.00% compounded monthly. At the end of the ten years the bond issuer will also pay back the original face value of the bond.

Formula (1- 06/12)^120 + 1

Next, develop a cash flow table in MS Excel for this bond purchase. Identify the correct time period units to use in the cash flow table, the total number of time periods, and the correct dollar amounts for your cash inflows and outflows at each time period. Then develop a cash flow diagram in MS Excel using the cash flow table and a stacked column chart.

I have made a cash table, although I'm unclear if i have done it correctly. Is there anyway someone can share to me an excel answer of this question that I'm working on. Something i can use for future problems like this also.

Answer 1

The time period used will be in quarters. There will be 40 quarters (10 years * 4 quarters every year)

The cashflow table is given below. The second row contains the formula if any

Year	Quarter	Bond Purchase	Coupon Inflow	Bond Face Value Return	Total Cashflow
A	B	C	D=6000*(1+6/1200)^3-6000	E	F=C+D+E
	0	-6000			-6000.00
1	1		90.45		90.45
2		90.45		90.45
3		90.45		90.45
4		90.45		90.45
2	5		90.45		90.45
6		90.45		90.45
7		90.45		90.45
8		90.45		90.45
3	9		90.45		90.45
10		90.45		90.45
11		90.45		90.45
12		90.45		90.45
4	13		90.45		90.45
14		90.45		90.45
15		90.45		90.45
16		90.45		90.45
5	17		90.45		90.45
18		90.45		90.45
19		90.45		90.45
20		90.45		90.45
6	21		90.45		90.45
22		90.45		90.45
23		90.45		90.45
24		90.45		90.45
7	25		90.45		90.45
26		90.45		90.45
27		90.45		90.45
28		90.45		90.45
8	29		90.45		90.45
30		90.45		90.45
31		90.45		90.45
32		90.45		90.45
9	33		90.45		90.45
34		90.45		90.45
35		90.45		90.45
36		90.45		90.45
10	37		90.45		90.45
38		90.45		90.45
39		90.45		90.45
40		90.45	6000	6090.45

Do let me know in case of any clarifications.

If you found this helpful, please rate it so that I can have higher earnings at no extra cost to you. This will motivate me to write more.