My Math Forum  

Go Back   My Math Forum > Science Forums > Economics

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


Reply
 
LinkBack Thread Tools Display Modes
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!
briansone is offline  
 
October 24th, 2014, 08:20 PM   #2
Math Team
 
Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 10,473
Thanks: 693

Quote:
Originally Posted by briansone View Post
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.
Denis is offline  
October 28th, 2014, 10:40 PM   #3
Member
 
AbrahamA's Avatar
 
Joined: May 2014
From: Rawalpindi, Punjab

Posts: 69
Thanks: 5

Excel YTM function

Quote:
Originally Posted by briansone View Post
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
		
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 10:43 PM.
AbrahamA is offline  
October 28th, 2014, 10:54 PM   #4
Member
 
AbrahamA's Avatar
 
Joined: May 2014
From: Rawalpindi, Punjab

Posts: 69
Thanks: 5

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

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
AbrahamA is offline  
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.
briansone is offline  
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!
briansone is offline  
August 21st, 2017, 06:30 PM   #7
Newbie
 
Joined: May 2017
From: Overland Park

Posts: 2
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.
mathhatter is offline  
Reply

  My Math Forum > Science Forums > Economics

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 05:36 PM
Maximum Yield mathkid Calculus 1 November 9th, 2012 08:16 PM
Projected Annual Yield keystone Economics 1 July 6th, 2012 07:14 PM
agricultural yield - applied optimization johnny172 Calculus 1 November 7th, 2009 11:03 AM
agricultural yield - applied optimization johnny172 Calculus 0 October 29th, 2009 01:47 PM





Copyright © 2017 My Math Forum. All rights reserved.