please have the excel formula ezto.mheducation.com Bond Pricing - Excel ILE HOME
ID: 2535834 • Letter: P
Question
please have the excel formula
ezto.mheducation.com Bond Pricing - Excel ILE HOME INSERT PAGE LAYOUT FORMULAS A REVIEW VIEW Sign In Calioni 2, ? . Alignment Number Conditional Format as Cel Cll Editing Fomatting Table Styles r u. Clipboard Tannary1, Ruiz C issucd bonds as follows 2 Face Amount 3 Number of Ycars: 4 Stated Interest Rate: R% Interest payments per 6 Note: the bonds pay interest semi aally.) Required 91 Given the different market interest rates below, calenlate the following items. Calculate the bond selling price USING THE EXCELPV FUNCTION (fx).Note: Enter al argumenrs as cell refevences. 9% 3a) Market Interest Rate 4 Annual Interest P 15 Band Selling Price: 6.0% T b) Market Interest Rate: 8Annunl Interest Payment 9 Bond Selling Price 21 2. Use the Excel IF function to wer either "Premiu" or "Discount" to the following items. 3 The bond in (a) sold at a 24 Tbe bond in (b) sold at a Sheeti +60% Attemptfs)Explanation / Answer
1. (a) annual interest payment: face value*int rate
= 500,000*8%=40000
Formula =B2*B4
bond selling price will be present value of all the cash inflows.
=PV(rate,nper,pmt,fv,type)
type= 0 if interest paid at end of the period
type = 1 if interest is paid begining of month
=-PV(B13,B5,(B2*B4),B2,0) i.e. =-PV(9%,,2,(500000*8%),500000,0)= 491,204.44
(b) by following same above
annual interest payment =B2*B4= 500000*8%=40000
Bond selling price (same as above)= -PV(B17,B5,(B2*B4),B2,0)= -(6%,2,(500000*8%),500000,0)= 518,333.93
2. =IF((B15-B2)<0,"Discount",IF((B15-B2)>0,"Premium","PAR"))
in above case B15 will be value of bond. So in first case it willbe discount and in second case it will be premium
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.