Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server multiples of invested capital


MOIC

Updated: 21 December 2012


Use the aggregate function MOIC to calculate the multiple of invested capital.
Syntax
XLeratorDB syntax for MOIC function for SQL Server
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
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service