Replicating Matlab XIRR function in Excel
10 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
How can I replicate Matlab XIRR in excel? I have tried the following but still couldn't get it to work.
cf=[-10,6,6,5]
df=[datenum('1/31/2000'),datenum('6/1/2000'),datenum('12/1/2000'),datenum('6/1/2001')]
xirr(cf,df)
In Excel, I have tried to create the discount factors using 1/(1+IRR)^[(date_i-date_1)/366] for the first three cashflows where date_i is the date on ith cashflow and date_1 is date for 1st cashflow i.e. 31st Jan 2000.
I have put the discount factor for the last cashflow to be 1/(1+IRR)^[("1/1/2011"-date_1)/366+(date_4-"1/1/2011")/365].
Using the sumproduct of the discount factors and the cashflow vector [-10,6,6,5], I get the Present Value.
Using the goal seek functionality in Excel to set the Present Value as 0 by varying IRR, I get an answer of 1.033499 (with max iteration of 200 and max change of 0.00000001).
Could you let me know if I have done anything wrongly? Or provide further documentation on the formula that Matlab actually calculates the XIRR?
Thanks!
0 commentaires
Réponses (0)
Voir également
Catégories
En savoir plus sur Data Export to MATLAB dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!