MOIC
Updated: 21 December 2012
Use the aggregate function MOIC to calculate the multiple of invested capital.
Syntax
Arguments
@Amt
the invested capital, distributions and residual value . @Amt is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
· @Amt values less than zero are treated as invested capital.
· @Amt values greater than zero are treated as distribution and invested capital.
Examples
In this example we have cash flows occuring in sporadically from 30-Nov-2011 through to 06-Feb-2014.
SELECT wct.MOIC(cf_amt) as MOIC
FROM (VALUES
(-100000, '11/30/2011','Investment'),
(-50000, '3/15/2012','Investment'),
(-2500, '7/18/2012','Investment'),
(12500, '11/30/2012','Distribution'),
(37500, '1/23/2013','Distribution'),
(75000, '4/30/2013','Distribution'),
(90000, '2/6/2014','Residual Value')
) n(cf_amt, cf_date, descr)
This produces the following result.
MOIC
----------------------
1.40983606557377
In this example, there are investments in several funds and we want to calculate the multiple of invested capital for each fund.
SELECT id_fund
,wct.MOIC(cf_amt) as MOIC
FROM (VALUES
('fund_4',52648.79,'2011-07-26','Distribution'),
('fund_2',24139.12,'2010-03-15','Distribution'),
('fund_4',-7753.76,'2009-12-03','Investment'),
('fund_3',-79186.02,'2009-05-18','Investment'),
('fund_1',3066.05,'2008-07-07','Distribution'),
('fund_4',-31117.56,'2009-04-23','Investment'),
('fund_4',-97759.99,'2010-05-25','Investment'),
('fund_3',94854.52,'2009-01-10','Distribution'),
('fund_1',70723.28,'2008-08-06','Distribution'),
('fund_2',55094.54,'2012-01-17','Distribution'),
('fund_1',-47405.78,'2012-06-27','Investment'),
('fund_3',90918.52,'2009-05-06','Distribution'),
('fund_1',-64781.66,'2009-06-02','Investment'),
('fund_2',42763.04,'2008-02-08','Distribution'),
('fund_2',-82375.82,'2012-04-13','Investment'),
('fund_4',-48254.35,'2011-01-01','Investment'),
('fund_2',-14354.14,'2009-01-15','Investment'),
('fund_1',-28037.22,'2010-06-14','Investment'),
('fund_2',-91220.67,'2010-07-27','Investment'),
('fund_3',-35367.08,'2008-03-29','Investment'),
('fund_1',108572.27,'2012-11-30','Residual Value'),
('fund_2',02267.47,'2012-11-30','Residual Value'),
('fund_3',123225.51,'2012-11-30','Residual Value'),
('fund_4',122355.01,'2012-11-30','Residual Value')
) n(id_fund, cf_amt, cf_date, descr)
GROUP BY id_fund
This produces the following result.
id_fund MOIC
------- ----------------------
fund_1 1.30049593274107
fund_2 0.661153250723342
fund_3 2.69742634638434
fund_4 0.946551506482439