EXCEL VBA 金融建模 -計算債券到期收益率(Eng)

Calculate the redemption yield of a bond via thebisection method and VBA.

The yield to maturity of a bond isn’t given by asimple, explicit equation – you need iterative methods to backsolve the bond pricingformula.

Excel’s RATE function,for example, iteratively calculate bond yields. However, you might want tocompute this quantity with VBA instead.

But why would you use VBA when RATE already exists?Well, there are several good reasons.

• You     learn theory behind     the bisection method and apply the concepts to a practical     problem
  
• You     can leverage your new found knowledge to solve other tricky numerical     problems, such as calculating implied     volatility
  
• You     gain familiarity with VBA – a widely used tool in business and finance     (you can’t call yourself an Excel guru without knowing at least a little     VBA)
  

This equation describes the price of a bond.

• C is     the coupon
  
• i is     the yield to maturity
  
• M is     the par value
  
• F is     the payment frequency
  
• n is     the number of payments
  

This equation cannot be rearranged to give i. Sohere’s some VBA that applies the bisection method to solve this equation for i

連結

http://investexcel.net/wp-content/uploads/2017/05/YieldToMaturityVBABisectionMethod.zip

由於上載附件過久也未能上傳得到，所以我只給連結了