Excel NPV Function, Yield Calculations, Credit Spreads, Duration, and Pricing Bonds

NPV Function and Cash Flows in Excel

  • The NPV (Net Present Value) function in Excel can be applied to all cash flows.

    • Important to combine the final period NPV with the present value of expected payments.

    • Discount the cash flow of $9.22 500 at the end of the period.

  • Note on Discount Rate:

    • Used a constant discount rate of 6%; for semiannual calculations, divide by 2 to get 3%.

    • The NPV function is valid for a single discount rate.

    • If multiple discount rates are involved, the NPV function cannot be used; instead, discounted cash flows need to be calculated step-by-step.

Calculating Yields in Excel

  • Yield to maturity can be calculated using the RATE function in Excel.

    • Ensure timing components are correct for bonds (e.g., semiannual payments).

  • Key Points in Yield Calculation:

    • Promised contractual amounts are used to determine yields.

    • Coupon payment example: $25,000 every six months.

    • Present Value (PV) should be entered as a negative number in Excel to represent cash outflow (cost of the bond).

  • Future Value (FV):

    • Include the promised face value payment of $1,000,000 in the FV input for the rate function.

  • Calculation involves assuming an investor pays $833,051 for the bond that promises periodic $25,000 payments and a million-dollar payoff at maturity.

  • Adjustments for Annual Rates:

    • Convert semiannual yield to annual by multiplying by 2 (yield of 7.39% in this case).

Comparison of IRR and RATE Functions

  • The yield to maturity parallels the Internal Rate of Return (IRR) concept.

    • Calculating IRR provides the same yield number but conceptualizes the cash flows:

    • Initial investment is the upfront cost (e.g., $833,051).

    • Cash inflows include periodic cash flows ($25,000) for twenty semiannual periods and the final payment of $1,025,000.

    • Using the IRR function requires flipping periods to account for semiannual estimations.

    • While IRR is functionally equivalent to the RATE, it is perceived as a more complex approach and is best avoided for simpler analyses.

Discussion on advantages of IRR vs. RATE

  • Utilizing IRR can allow sophisticated analyses, particularly when considering different repayment probabilities at various times.

    • Example: If the first two years have a 10% chance of zero payments and 90% certainty thereafter, IRR can address these uncertainties effectively.

Credit Spread Overview

  • Credit spreads relate to differences in yields between corporate bonds and treasury bonds of identical maturities, important for risk assessment.

    • Example: Two bonds maturing in one year:

    • One government-backed (Treasury bond) with a known repayment.

    • One corporate bond with uncertain repayment.

  • Credit spread calculated as difference in yields; treasury yield example: 4% vs. corporate yield: 15.5%, resulting in an 11.5% spread.

  • Conceptual Basis for Credit Spread:

    • Yields reflect both timing and risk adjustments.

    • Treasury yields reflect just the timing risk, while corporate yields encompass both timing and uncertainty risks.

Duration and Interest Rate Risk

  • Duration relates to sensitivity to interest rate changes; bonds with longer maturities or without coupon payments exhibit greater price sensitivity to interest rate fluctuations.

    • Example: A ten-year zero-coupon bond price decreases significantly with interest rate increases versus a short-term bond.

  • Coupon bonds provide a cushion against interest rate changes due to earlier cash flow recognition across periods.

Bond Pricing and Financial Management

  • Firms can adjust the face value and coupon rates of bonds to align with required yield benchmarks determined through market interactions.

  • Semiannual payments lead to explicit annual interest expenses reflected on corporate income statements, calculated based on the coupon rate and bond issuance amount.

  • Importance of these components in corporate finance pertains to investor decisions and firm capital management strategies.

Conclusion and Study Resources

  • Summary slides available on e-learning platforms capture critical concepts covered in the lecture for preparation for examinations and deeper understanding of financial instruments and risk assessments.

  • Discussion encourages continued engagement in finance practices and acknowledges complexity in real-world applications of theoretical principles.