User Name Remember Me? Password

 Economics Economics Forum - Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance

 October 24th, 2014, 04: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, 08:20 PM   #2
Math Team

Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 14,597
Thanks: 1038

Quote:
 Originally Posted by briansone How do you calculate the annualised compounding rate of return?
Get some future value, then calculate what rate achieved that future value...
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, 10:40 PM   #3
Member

Joined: May 2014
From: Rawalpindi, Punjab

Posts: 69
Thanks: 5

Excel YTM function

Quote:
 Originally Posted by briansone 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!

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 )
Example Usage

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

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

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

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
Exit Function
End If
x0 = x
Next i
tadYIELD = (-1) ^ (0.5)
End Function

Last edited by AbrahamA; October 28th, 2014 at 10:43 PM. October 28th, 2014, 10:54 PM   #4
Member

Joined: May 2014
From: Rawalpindi, Punjab

Posts: 69
Thanks: 5

Quote:
 Originally Posted by briansone 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.
You would need to use Excel XIRR function given a schedule of transaction dates and money amounts

Syntax
Code:
tadXIRR(Cash flows , Transaction Dates, guess, compounding )
But such annual compounding rate of return calculation is the most trivial one

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

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

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
Else
tadXIRR = (-1) ^ (0.5)
End If

End Function December 7th, 2014, 08: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, 04: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, 06: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 Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Similar Threads Thread Thread Starter Forum Replies Last Post Fitim Economics 1 October 28th, 2013 05:36 PM mathkid Calculus 1 November 9th, 2012 08:16 PM keystone Economics 1 July 6th, 2012 07:14 PM johnny172 Calculus 1 November 7th, 2009 11:03 AM johnny172 Calculus 0 October 29th, 2009 01:47 PM

 Contact - Home - Forums - Cryptocurrency Forum - Top      