时间戳

首页 > 解决方案 > 如何计算每月回报的总回报

tsql - 如何计算每月回报的总回报

问题描述

我有一张像下面这样有每月回报的表格。我的目标是计算 10000 美元的投资增长。下面显示的投资增长列是我手动计算的。第一个月，10000*(1+return/100) 将是第一个值。结果值将代入公式中，用于下个月的回报，依此类推。

有人可以帮助我查询以实现此目的。

谢谢

【月收益表——投资增长栏目是目标】

在此处输入图像描述

标签： tsqlssmsrecursive-query

解决方案

您可以使用递归 CTE 执行此操作，该 CTE 循环遍历您的表并可以引用先前计算的行。但是请注意，由于这种逐行循环，它可能会根据您的环境运行相当缓慢：

declare @t table (FundName nvarchar(5),ReturnRate decimal(5,2), EndDate date);
insert into @t values('FundA',1.2,'20171231'),('FundA',-0.7,'20180131'),('FundA',1.3,'20180228'),('FundA',-2.3,'20180331'),('FundA',2.5,'20180430'),('FundA',1.9,'20180531'),('FundA',0.8,'20180630'),('FundB',1.1,'20171231'),('FundB',-0.4,'20180131'),('FundB',1.6,'20180228'),('FundB',-1.3,'20180331'),('FundB',2.0,'20180430'),('FundB',0.9,'20180531'),('FundB',0.8,'20180630');

declare @InitialInvestment int = 10000;

with rn as        -- Apply a row number to each Fund, ordered by EndDate so the first row can be used as the anchor in the Recursive CTE below.
(
    select FundName
            ,ReturnRate
            ,EndDate
            ,row_number() over (partition by FundName order by EndDate) as rn
    from @t
)
,r as
(
    select FundName        -- Select just the first rows for each fund and calculate the Investment Growth
            ,ReturnRate
            ,EndDate
            ,rn
            ,cast(@InitialInvestment * (1 + (ReturnRate/100)) as decimal(20,10)) as InvestmentGrowth
    from rn
    where rn = 1

    union all

    select r.FundName      -- Then row by row, fetch the next and calculate the Investment Growth by referencing the previous row in the dataset
            ,rn.ReturnRate
            ,rn.EndDate
            ,rn.rn
            ,cast(r.InvestmentGrowth * (1 + (rn.ReturnRate/100)) as decimal(20,10)) as InvestmentGrowth
    from r
        join rn
            on r.FundName = rn.FundName
                and r.rn = rn.rn-1
)
select FundName
    ,ReturnRate
    ,EndDate
    ,InvestmentGrowth
from r
order by FundName
        ,EndDate;

输出：

+----------+------------+------------+------------------+
| FundName | ReturnRate |  EndDate   | InvestmentGrowth |
+----------+------------+------------+------------------+
| FundA    |       1.20 | 2017-12-31 | 10120.0000000000 |
| FundA    |      -0.70 | 2018-01-31 | 10049.1600000000 |
| FundA    |       1.30 | 2018-02-28 | 10179.7990800000 |
| FundA    |      -2.30 | 2018-03-31 |  9945.6637011600 |
| FundA    |       2.50 | 2018-04-30 | 10194.3052936890 |
| FundA    |       1.90 | 2018-05-31 | 10387.9970942691 |
| FundA    |       0.80 | 2018-06-30 | 10471.1010710233 |
| FundB    |       1.10 | 2017-12-31 | 10110.0000000000 |
| FundB    |      -0.40 | 2018-01-31 | 10069.5600000000 |
| FundB    |       1.60 | 2018-02-28 | 10230.6729600000 |
| FundB    |      -1.30 | 2018-03-31 | 10097.6742115200 |
| FundB    |       2.00 | 2018-04-30 | 10299.6276957504 |
| FundB    |       0.90 | 2018-05-31 | 10392.3243450122 |
| FundB    |       0.80 | 2018-06-30 | 10475.4629397723 |
+----------+------------+------------+------------------+

推荐阅读