Please help me to set fomula to calculate the lump sum bond

[hunterjo84]

hello.

I am working to make a bond template which is lump sum principal payment at the end.

I set up cells for period as =IF(ROWS(A$22:A22)-1>B$9,"",ROWS(A$22:A22)-1), so once I put the number of payment the excel will generate number of periods for me automatically.

Also I used IF function for the PMT and Balance.

The problem is that this bond needs to pay off principal at the end of the amortization schedule(at the maturity date).

Can you help me to set it up?

just in case I give you information for the bond.
Face amount 5,000,000 Stated rate 6% semi-annual payment
bond term is 10years.
there is no market rate so I calculate the IRR.

Please help me out

Thanks

 

[gsistek]

Wouldn't the lump sum at the end be the face value of 5,000,000 or are the semi-annual coupons reinvested into the bond?