Replicating Matlab XIRR function in Excel

10 visualizaciones (últimos 30 días)
Zhao Wei Yee
Zhao Wei Yee el 19 de Oct. de 2015
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!

Respuestas (0)

Categorías

Más información sobre Data Export to MATLAB en Help Center y File Exchange.

Etiquetas

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by