strange bndyield function result vs excel

when i'll running this simple bndyieal function on matlab:

bndyield(50,0,'16-Dec-2022','16-Dec-2023','Period',1,'Basis',1, 'DiscountBasis',1)

i get a result of 0.8284

when i'm using excel formula of Yield for the same input, i get the correct value of 1. what is the problem?

=YIELD("16-Dec-2022","16-Dec-2023",0,50,100,1)

Answer 1

[Subhajyoti]

Hi @shlomo Kugler,

The differences while calculating the bond yields are occurring due to mismatched parameters, especially ‘Basis’ and ‘DiscountBasis’. When the ‘RedemptionValue’ is set to ‘100’ and both the ‘Basis’ and ‘DiscountBasis’ are configured to align with the Excel settings, then the MATLAB outputs match with the Excel results.

Here, in the following implementation, I have used a ‘30/360 Basis’ to match the default Basis in Excel.

Price = 95.04287;
CouponRate = 0.05750000; % 5.75%
Settle = datetime('15-Feb-2008');
Maturity = datetime('15-Nov-2016');
Period = 2;
Basis = 1; % 30/360

% Matches Excel YIELD for RV of 100
% =YIELD("15-Feb-2008","15-Nov-2016",5.75%,95.04287,100,2,0)
bondYeild = bndyield(Price, CouponRate, Settle, Maturity,'Basis',1,'DiscountBasis',1, 'Period',Period)

bondYeild = 0.0650

The above result matches with the output of the excel formula:

=YIELD("15-Feb-2008","15-Nov-2016",5.75%,95.04287,100,2,0)

The above implementation for ‘YEILD’ function is elaborated in the following Excel Documentation:

https://support.microsoft.com/en-us/office/yield-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe

You can refer to the following MathWorks documentation links to learn more about ‘Pricing and Computing Yields for Fixed-Income Securities’ in MATLAB:

• https://www.mathworks.com/help/finance/bndyield.html
• https://www.mathworks.com/help/finance/pricing-and-computing-yields-for-fixed-income-securities.html

I hope this helps.