Prev: Compile error in hidden module
Next: error : CANNOT START THE SOURCE APPLICATION FOR THIS OBJECT
From: Jeff on 26 May 2006 10:46
Hi,

I have dates in column A, and payments into a fund in column B. The
payments are not uniform and the dates are random, so I want to calculate the
IRR, based on todays date. One way is to put in column C the payment and the
formula

B1*(1+$D$1^(Today()-A1)/365)) for all the rows and do a goal seek on D1.
The goal seek on the value today, which I know + comes from another source.

Is there a way to do this in one formula, I tried using arrays and
sumproduct but could not figure anything out?

Thanks for your help.
From: Paul Lautman on 26 May 2006 11:08
Jeff wrote:
> Hi,
>
> I have dates in column A, and payments into a fund in column B. The
> payments are not uniform and the dates are random, so I want to
> calculate the IRR, based on todays date. One way is to put in column
> C the payment and the formula
>
> B1*(1+$D$1^(Today()-A1)/365)) for all the rows and do a goal seek on
> D1.
> The goal seek on the value today, which I know + comes from another
> source.
>
> Is there a way to do this in one formula, I tried using arrays and
> sumproduct but could not figure anything out?
>
> Thanks for your help.

But you are only showing us one formula?????


From: Fred Smith on 27 May 2006 01:13
You want the XIRR function. It's in the Analysis Toolpak which you may need to
load with Tools>Addins.

Feed it your cash flows and your dates and, voila, it will return the IRR.

--
Regards,
Fred


"Jeff" <Jeff(a)discussions.microsoft.com> wrote in message
news:A1418254-E51C-4B2D-99FD-87B6025217CE(a)microsoft.com...
> Hi,
>
> I have dates in column A, and payments into a fund in column B. The
> payments are not uniform and the dates are random, so I want to calculate the
> IRR, based on todays date. One way is to put in column C the payment and the
> formula
>
> B1*(1+$D$1^(Today()-A1)/365)) for all the rows and do a goal seek on D1.
> The goal seek on the value today, which I know + comes from another source.
>
> Is there a way to do this in one formula, I tried using arrays and
> sumproduct but could not figure anything out?
>
> Thanks for your help.


 | 
Pages: 1
Prev: Compile error in hidden module
Next: error : CANNOT START THE SOURCE APPLICATION FOR THIS OBJECT