Question

Can you please help solve the formula portions and show theformulas used?

PROBLEM DATA

The University Club recently issued $1,500,000 of 10-year, 9%bonds at an effective interest rate of 10%. Bond interest ispayable annually.

REQUIREMENTS

1. You have been asked to calculate the issuance price of thebonds and prepare amortization schedules for any discount orpremium. The worksheet BONDS has been provided to assist you. Notethat the worksheet contains a scratch pad at the bottom that hasbeen preprogrammed to automatically compute and display therelevant cash flows needed for bond pricing.

2. The bond pricing formula will utilize the NPV (Net PresentValue) function on your spreadsheet program. The NPV function willautomatically compute the net present value of annual future cashflows discounted at a specific interest rate. The interest rateshould be expressed as a cell address.

BONDS
Bond Pricing andAmortization
Data Section
	Face value of bond			$1,500,000
	Years to maturity *			10
	Stated interest rate			9.0%
	Effective interest rate			10.0%
	* Worksheet is designed for use with bonds having amaturity of
	12 years or less and paying interestannually.
Answer Section
	Bond issue price				FORMULA1
Amortization Schedule -Straight Line Method
	Cash		Interest	(Disc.)	Bond
Year	Paid	Amortization	Expense	Premium	Carrying Value
0				FORMULA2	FORMULA3
1	FORMULA4	FORMULA5	FORMULA6	FORMULA7	FORMULA8
2	FORMULA9	FORMULA10	FORMULA11	FORMULA12	FORMULA13
3	0	0	0	0	0
4	0	0	0	0	0
5	0	0	0	0	0
6	0	0	0	0	0
7	0	0	0	0	0
8	0	0	0	0	0
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	0
12	0	0	0	0	0
Amortization Schedule -Effective Interest Method
	Interest	Cash		(Disc.)	Bond
Year	Expense	Paid	Amortization	Premium	Carrying Value
0				FORMULA14	FORMULA15
1	FORMULA16	FORMULA17	FORMULA18	FORMULA19	FORMULA20
2	FORMULA21	FORMULA22	FORMULA23	FORMULA24	FORMULA25
3	0	0	0	0	0
4	0	0	0	0	0
5	0	0	0	0	0
6	0	0	0	0	0
7	0	0	0	0	0
8	0	0	0	0	0
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	0
12	0	0	0	0	0
	Scratch Pad
	Display of relevant cash flows
		Annual	Bond
	Year	Interest	Maturity
	1	135000	0
	2	135000	0
	3	135000	0
	4	135000	0
	5	135000	0
	6	135000	0
	7	135000	0
	8	135000	0
	9	135000	0
	10	135000	1500000
	11	0	0
	12	0	0