Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Duration with UDF
I just developed a simple duration function:
Function Dur(PDown, PUp, P, DelY) Dur = (PDown - PUp) / (2 * P * DelY) End Function This is based on the sample he http://thismatter.com/money/bonds/du...-convexity.htm My duration function is giving a result about 6.96% different than the Excel built in duration function. I cant figure out why mine is wrong. Can someone please tell me. My assumptions: P-Down = $1,035.94 P-Up = $965.68 P = 1000 Delta-Y = 0.005 Excel Assumptions: Bond Settlement = 01/02/2000 Bond Maturity = 01/02/2010 YTM = 7% I'm just playing around with my own UDF, that's all this is... Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Duration with UDF
I think it was off just a bit because I was calculating duration using two
slightly different methods. I'm trying this UDF now: Function Dur(CF, t, i, n, M, C) 't = time to maturity 'C = cash flow 'i = required yield 'n = number of cash flows 'M = maturity (par) value 'P = bond price Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1 / ((1 + i) ^ n))) / i) + M / (1 + i) ^ n End Function It's based on the function he http://www.investopedia.com/universi...ancedbond5.asp I'm getting some REALLY crazy results now!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I just developed a simple duration function: Function Dur(PDown, PUp, P, DelY) Dur = (PDown - PUp) / (2 * P * DelY) End Function This is based on the sample he http://thismatter.com/money/bonds/du...-convexity.htm My duration function is giving a result about 6.96% different than the Excel built in duration function. I cant figure out why mine is wrong. Can someone please tell me. My assumptions: P-Down = $1,035.94 P-Up = $965.68 P = 1000 Delta-Y = 0.005 Excel Assumptions: Bond Settlement = 01/02/2000 Bond Maturity = 01/02/2010 YTM = 7% I'm just playing around with my own UDF, that's all this is... Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Duration with UDF
On 2/18/2010 4:01 PM, ryguy7272 wrote:
I think it was off just a bit because I was calculating duration using two slightly different methods. I'm trying this UDF now: Function Dur(CF, t, i, n, M, C) 't = time to maturity 'C = cash flow 'i = required yield 'n = number of cash flows 'M = maturity (par) value 'P = bond price Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1 / ((1 + i) ^ n))) / i) + M / (1 + i) ^ n End Function It's based on the function he http://www.investopedia.com/universi...ancedbond5.asp I'm getting some REALLY crazy results now!! Hi. I don't get the same "equation" as listed from your link... http://www.investopedia.com/universi...ancedbond5.asp You didn't mention what your results were, and what you were expecting. I'm just playing around with my own UDF, that's all this is... Here is my interpretation of the equation of the link... Things can be reduced (like removing the loop), but I'll just follow the equation as best I can... Sub TestIt() 'Excel 2007 Help on "Duration" Debug.Print Duration(8, 2, 0.08, 0.09) 'Your Link Reference Debug.Print Duration(5, 1, 0.05, 0.05) End Sub Function Duration(Years, NPer, CRate, Yield) ' = = = = = = = = = = = = = = = = = = '// NPer is the number of periods per year Const Face As Double = 1000 Dim BondPrice As Double Dim Pc As Double Dim Py As Double Dim Num As Double 'Numerator Dim t As Long Dim Np As Double Dim Ny As Double Dim UL As Double 'Upper limit, ie..number of payments. Dim C As Double 'Cash Flow ' = = = = = = = = = = = = = = = = = = Pc = CRate / NPer Py = Yield / NPer UL = Years * NPer C = Face * Pc / NPer BondPrice = (Face * Pc) * ((1 - 1 / (1 + Py) ^ UL) / Py) + Face / (1 + Py) ^ UL For t = 1 To UL Num = Num + (t * C) / (1 + Py) ^ t Next t Num = Num + (Face * UL / NPer) / (1 + Py) ^ UL Duration = Num / BondPrice '// If you wish to Round... Duration = Round(Duration, 2) End Function Results a 5.99377495554519 4.54595050416236 Or rounded... 5.99 4.55 Notice that Excel 2007 help on 'Duration gave a result to 6 decimal places of: 5.993775 which matches with the above equation. Again, we could clean this up a little, but I think you wanted to follow along. = = = = = = = HTH :) Dana DeLouis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Duration with UDF
On 2/18/2010 4:01 PM, ryguy7272 wrote:
Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1 / ((1 + i) ^ n))) / i) + M / (1 + i) ^ n Hi. Here's my attempt at reducing the loop... Sub TestIt() 'Excel 2007 Help on "Duration" Debug.Print Duration(8, 2, 0.08, 0.09) 'Your Link Reference Debug.Print Duration(5, 1, 0.05, 0.05) End Sub Function Duration(Years, n, c, y) Dim P As Double Dim k As Double k = n + y P = Years * n Duration = (k/y - ((k + P * (c - y)) /(c * ((k / n) ^ P - 1) + y)))/ n Duration = Round(Duration, 6) End Function Returns: 5.993775 4.545951 Which matches Excel's solution for the Function "Duration" and the example from your link. = = = = = = = HTH :) Dana DeLouis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Duration with UDF
Here's what I ended up with:
Function Dur(CF, t, i, n, M, C) Dim sumall As Double Dim j As Integer Dim ttm As Integer sumall = 0 For j = 1 To n ttm = t - j + 1 sumall = sumall + ((CF * ttm) / (1 + i) ^ ttm) Next j Dur = (sumall + ((n * M) / (1 + i) ^ n)) / ((C * ((1 - (1 / ((1 + i) ^ n))) / i) + (M / (1 + i) ^ n))) End Function It matches Excel's built-in Duration function. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dana DeLouis" wrote: On 2/18/2010 4:01 PM, ryguy7272 wrote: Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1 / ((1 + i) ^ n))) / i) + M / (1 + i) ^ n Hi. Here's my attempt at reducing the loop... Sub TestIt() 'Excel 2007 Help on "Duration" Debug.Print Duration(8, 2, 0.08, 0.09) 'Your Link Reference Debug.Print Duration(5, 1, 0.05, 0.05) End Sub Function Duration(Years, n, c, y) Dim P As Double Dim k As Double k = n + y P = Years * n Duration = (k/y - ((k + P * (c - y)) /(c * ((k / n) ^ P - 1) + y)))/ n Duration = Round(Duration, 6) End Function Returns: 5.993775 4.545951 Which matches Excel's solution for the Function "Duration" and the example from your link. = = = = = = = HTH :) Dana DeLouis . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate duration (#yrs., # mos.) | Excel Worksheet Functions | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate duration day +1 | Excel Worksheet Functions | |||
Calculate Duration. | Excel Discussion (Misc queries) |