
Economics Economics Forum  Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance 
 LinkBack  Thread Tools  Display Modes 
October 24th, 2014, 05:27 PM  #1 
Newbie Joined: Oct 2014 From: Sydney Posts: 3 Thanks: 0 Math Focus: financial  Yield to maturity
Hi Everyone & thanks in advance for any help on this... Suppose you know:  Instrument price (market price)  Future coupon dates & amounts (not predictable, but you have the data)  Principal repayment amount & date... (& you have Microsoft excel/VBA) How do you calculate the annualised compounding rate of return? I know if the coupons could be treated as an annuity it's easy, but that's the sticky part here  the coupon amounts/dates are all over the place, albeit known. Any help greatly appreciated! 
October 24th, 2014, 09:20 PM  #2  
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 13,638 Thanks: 954  Quote:
that's about all I can tell you. If you want a better answer, than ask a better/clearer question; and if possible, supply an example. Merci beaucoup.  
October 28th, 2014, 11:40 PM  #3  
Member Joined: May 2014 From: Rawalpindi, Punjab Posts: 69 Thanks: 5  Excel YTM function Quote:
Edit the following code for a schedule of coupon payments and their transaction dates Syntax Code: tadYIELD(settlement date , maturity date , coupon rate , price , redemption , freq , guess ) Code: Settlement Date 6/15/2014 6/15/2014 Maturity Date 2/15/2018 2/15/2018 Annual Coupon 4% 4% Price 1100 110 Redemption 1000 100 Freq 2 2 Basis 30/360 30/360 tadYIELD 1.2035% 1.2035% Excel YIELD 2.2035% 1.2035% Code: Public Function tadPVIF(ByVal i As Double, ByVal N As Double) As Double tadPVIF = (1 + i) ^ (N) End Function Public Function tadPVIF_differential(ByVal i As Double, ByVal N As Double) As Double tadPVIF_differential = N * tadPVIF(i, N + 1) End Function Public Function tadPrice(ByVal sd As Long, ByVal md As Long, ByVal crate As Double, ByVal yld As Double, ByVal redemption As Double, ByVal freq As Integer) Dim DSC As Integer Dim A As Integer Dim E As Integer Dim N As Double Dim numofcoupons As Integer Dim Nfrac As Double Dim K As Integer Dim price As Double price = 0 DSC = WorksheetFunction.CoupDaysNc(sd, md, 2) A = WorksheetFunction.CoupDayBs(sd, md, 2) E = WorksheetFunction.CoupDays(sd, md, 2) N = WorksheetFunction.Days360(sd, md, 2) \ (360 \ freq) If (A <> 0) Then N = N + 1 End If If (Nfrac = 0) Then numofcoupons = N Else numofcoupons = N  Nfrac + 1 End If price = redemption * tadPVIF(yld / freq, N  1 + (DSC / E)) For K = 0 To N  1 price = price + redemption * crate / freq * tadPVIF(yld / freq, K + (DSC / E)) Next K price = price  redemption * crate / freq * A / E tadPrice = price End Function Public Function tadPrice_differential(ByVal sd As Long, ByVal md As Long, ByVal crate As Double, ByVal yld As Double, ByVal redemption As Double, ByVal freq As Integer) Dim DSC As Integer Dim A As Integer Dim E As Integer Dim N As Double Dim numofcoupons As Integer Dim Nfrac As Double Dim K As Integer Dim price As Double price = 0 DSC = WorksheetFunction.CoupDaysNc(sd, md, 2) A = WorksheetFunction.CoupDayBs(sd, md, 2) E = WorksheetFunction.CoupDays(sd, md, 2) N = WorksheetFunction.Days360(sd, md, 2) \ (360 \ freq) If (A <> 0) Then N = N + 1 End If If (Nfrac = 0) Then numofcoupons = N Else numofcoupons = N  Nfrac + 1 End If price = redemption * tadPVIF_differential(yld / freq, N  1 + (DSC / E)) For K = 0 To N  1 price = price + redemption * crate / freq * tadPVIF_differential(yld / freq, K + (DSC / E)) Next K tadPrice_differential = price End Function Public Function tadYIELD(ByVal sd As Long, ByVal md As Long, ByVal crate As Double, ByVal pr As Double, ByVal redemption As Double, ByVal freq As Integer, Optional guess As Double = 0.1) Dim f, f_differential, x, x0 As Double Dim i As Integer x0 = guess For i = 1 To 100 f = pr + tadPrice(sd, md, crate, x0, redemption, freq) f_differential = tadPrice_differential(sd, md, crate, x0, redemption, freq) If (f_differential = 0) Then tadYIELD = (1) ^ (0.5) Exit Function End If x = x0  f / f_differential If (Abs(x  x0) < 0.0000000001) Then tadYIELD = x Exit Function End If x0 = x Next i tadYIELD = (1) ^ (0.5) End Function Last edited by AbrahamA; October 28th, 2014 at 11:43 PM.  
October 28th, 2014, 11:54 PM  #4  
Member Joined: May 2014 From: Rawalpindi, Punjab Posts: 69 Thanks: 5  Quote:
Syntax Code: tadXIRR(Cash flows , Transaction Dates, guess, compounding ) For complex internal rate of return example calculations see this How to find IRR in Excel 2007, 2010 and 2013 Code: Public Function tadEFFECT(ByVal rate As Double, ByVal compounding As Double) If compounding = 0 Then tadEFFECT = Exp(rate)  1 Else tadEFFECT = (1 + rate * compounding) ^ (1 / compounding)  1 End If End Function Public Function tadPVIF(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double) tadPVIF = (1 + tadEFFECT(rate, compounding)) ^ (N) End Function Public Function tadPVIFbar(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double) If (compounding = 0) Then tadPVIFbar = N * tadPVIF(rate, N, compounding) Else tadPVIFbar = N / compounding * tadPVIF(rate, N, compounding) End If End Function Public Function tadXNPV(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double Dim i As Long Dim t As Double Dim npv As Double npv = 0 For i = 0 To UBound(ValuesArr) t = (DatesArr(i)  DatesArr(0)) / 365 npv = npv + ValuesArr(i) * tadPVIF(rate, t, compounding) Next i tadXNPV = npv End Function Public Function tadXNPVbar(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double Dim rCell As Range Dim i As Long Dim t As Double Dim npv As Double npv = 0 For i = 0 To UBound(ValuesArr) t = (DatesArr(i)  DatesArr(0)) / 365 npv = npv + ValuesArr(i) * tadPVIFbar(rate, t + compounding, compounding) Next i tadXNPVbar = npv End Function Public Function IsRealPower(ByRef DatesArr() As Long) As Boolean Dim i As Long Dim N As Double Dim IsReal As Boolean IsReal = False For i = 1 To UBound(DatesArr) N = (DatesArr(i)  DatesArr(0)) / 365 If (N  Int(N)) <> 0 Then IsReal = True Exit For End If Next i IsRealPower = IsReal End Function Public Function EducatedGuessXIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double Dim B As Double Dim C As Double Dim i As Long Dim N As Double Dim HPR As Double Dim AHPY As Double B = 0 C = 0 For i = 0 To UBound(ValuesArr) If ValuesArr(i) > 0 Then B = B + ValuesArr(i) Else C = C + Abs(ValuesArr(i)) End If Next i N = (DatesArr(UBound(DatesArr))  DatesArr(0)) / 365 If ((B <> 0) And (C <> 0)) OR (compounding <> 0) Then HPR = B / C AHPY = HPR ^ (compounding / N)  1 AHPY = AHPY / compounding EducatedGuessXIRR = AHPY Else EducatedGuessXIRR = guess End If End Function Public Function EducatedGuessIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double Dim B As Double Dim C As Double Dim i As Long Dim HPR As Double Dim HPY As Double B = 0 C = 0 For i = 0 To UBound(ValuesArr) If ValuesArr(i) > 0 Then B = B + ValuesArr(i) Else C = C + Abs(ValuesArr(i)) End If Next i If ((B <> 0) And (C <> 0)) OR (compounding <> 0) Then HPR = B / C HPY = HPR  1 HPY = HPY / compounding EducatedGuessIRR = HPY Else EducatedGuessIRR = guess End If End Function Public Function tadXIRR(ByVal Values As Range, ByVal Dates As Range, Optional ByRef guess As Double = 0.1, Optional ByRef compounding As Double = 1#) As Double Dim f As Double Dim fbar As Double Dim x As Double Dim x0 As Double Dim i As Integer Dim found As Integer Dim rCell As Range Dim ValuesArr() As Double Dim DatesArr() As Long ReDim ValuesArr(Values.Count  1) ReDim DatesArr(Values.Count  1) i = 0 For Each rCell In Values.Cells ValuesArr(i) = rCell.Value i = i + 1 Next rCell i = 0 For Each rCell In Dates.Cells DatesArr(i) = rCell.Value i = i + 1 Next rCell found = 0 i = 1 If guess = 0.1 Then If IsRealPower(DatesArr()) Then x0 = EducatedGuessXIRR(ValuesArr(), DatesArr(), guess, compounding) Else x0 = EducatedGuessIRR(ValuesArr(), DatesArr(), guess, compounding) End If Else x0 = guess End If Do While (i < 100) f = tadXNPV(x0, ValuesArr(), DatesArr(), compounding) fbar = tadXNPVbar(x0, ValuesArr(), DatesArr(), compounding) If (fbar = 0) Then tadXIRR = (0) ^ (1) Else x = x0  f / fbar End If If (Abs(x  x0) < 0.000001) Then found = 1 Exit Do End If x0 = x i = i + 1 Loop If (found = 1) Then tadXIRR = x Else tadXIRR = (1) ^ (0.5) End If End Function  
December 7th, 2014, 09:27 PM  #5 
Newbie Joined: Oct 2014 From: Sydney Posts: 3 Thanks: 0 Math Focus: financial 
thanks for the answers guys. (sorry it took so long to get back with a thanks). will work through the IRR & report back. 
January 4th, 2015, 05:50 PM  #6 
Newbie Joined: Oct 2014 From: Sydney Posts: 3 Thanks: 0 Math Focus: financial 
IRR does it perfectly. From what i can see on the internet, some IRR functions in previous Excel versions only allow input of (values,guess). I'm using Excel for Mac 2011 & it allows (values,dates,guess) but guess is optional. Thanks for your help! 
August 21st, 2017, 07:30 PM  #7 
Newbie Joined: May 2017 From: Overland Park Posts: 3 Thanks: 0 
Hello, yes, just confirming that the IRR function should be able to do what you asking. You are correct, different timing of the cash flows and amounts makes it tricky to calculate the IRR or Yield to Maturity. Not even most yield to maturity calculators have the ability to add different timing of coupon payments and different cash flow amounts. That's something where excel definitely has a leg up.


Tags 
bond, coupons, maturity, rate of return, yield 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Annual payment (maturity of the mortgage)  Fitim  Economics  1  October 28th, 2013 06:36 PM 
Maximum Yield  mathkid  Calculus  1  November 9th, 2012 09:16 PM 
Projected Annual Yield  keystone  Economics  1  July 6th, 2012 08:14 PM 
agricultural yield  applied optimization  johnny172  Calculus  1  November 7th, 2009 12:03 PM 
agricultural yield  applied optimization  johnny172  Calculus  0  October 29th, 2009 02:47 PM 