Hi all,
Can anybody explain me, how do you compute YTM in Excel, and how do you generate EWMA covariance matrix for large portfolio (let say - 40-50 stocks)? Yield function looks like bulsh…
What’s wrong with the Yield function in Excel? Once you start calculating EWMA matrices for 40-50 stocks, you probably need to use some other software than Excel.
The problem is for bonds with amortization. Any suggestions? And there is no option for calculating EWMA matrices for 40-50 stocks in Excel, I have no other choice?
What exactly is the problem with bond amortization? The asnwer is that either there is a built in Excel function that does it or you can write one pretty easily (or find it on the Internet). Excel is really good with things like this. So I guess that you want a EWMA covariance matrix yes? There might be some software out there that does it for excel, but what are you going to do with this giant matrix anyway? If you want just a few covarainces, it’s not hard to program that in Excel. The problem is that Excel is nogt a good programming language and it is not a good database and calculating a EWMA covariance matrix aand doing something with it requires both. It’s easy to program this in MatLab or SAS IML or Mathematica or (my favorite) Delphi or C# or …
Thank you Joey. The problem with bond amortization is that YIELD function does not return correct result after first principal repayment, don’t you think so? And my question is - hot do you compute YTM for amortizing bond?
I don’t believe there is a native Excel function to do that. If I was doing it 1-20 times, I would just use Excel’s IRR function, which requires you to put the cash flows into cells so for example if I bought the bond for 1000 and received 5 210 payments, I would enter -1000 in A1, 210 in A2, …, 210 in A6. Then in B1 I would enter =IRR(A1:A6) (which would be 2%). That would be the YTM for the amortizing bond. If I had to do it more times than that, I would write an Excel function to do it in VBA. That’s not going to be super easy because I have two choices - write a numerical solving routine to get the IRR or fill in cells in some worksheet and use the worksheet function to calculate the IRR. I couldn’t live with myself if I did the latter, so I would personally program up the secant method which works very well on problems like this. If you don’t know how the secant method works and how to program VBA function in Excel, this would be a pretty good starter project really.
IRR is not good for this occasion, because dates are very important in this case 
It is the same for XIIR, because it uses only 365 day - year. Maybe really the decision is VBA but will I can …
IRR is not good for this occasion, because dates are very important in this case It is the same for XIIR, because it uses only 365 day - year. Maybe really the decision is VBA but will I can …
mysterious, you can use solver to calculate YTM. As Joey pointed out, Excel is not designed to calculate EWMA covariance matrix. I like using Matlab. However, if you are looking for free software, Octave might be the right tool for you: http://www.octave.org/wiki/index.php?title=Main_Page