Efficient alternate to the cursor in TSQL 2012 to get last value for payment schedule calculation

Question

I am trying to derive an annuity payment plan. I know there are some solution available online but as I need to make changes to monthly payment amounts, I can't use those solutions and due to security policies I can't install new packages as well. Right now I am struggling to calculate the monthly opening balance as it depends on the capital calculated in the last row. Is there a more efficient way to do this other then the cursor. I have like 30 million rows right now.

Data I have:

This is a very simple example, there can be different rates, different monthly payments and dates missing. So I can't use the available solution online.

End result I want:

where:

• Interest = Opening Balance x Rate Charge
• Capital = Monthly repayment - Interest
• Opening Balance = Last Opening Balance - Last Capital

I have tried to use LAG but it does not work as LAG is a deterministic function.

Here is the script for the data with account.

CREATE TABLE [temp_da_test].[example_3](
    [Account] [nvarchar](255) NULL,
    [Month] [date] NULL,
    [Rate Charge] [float] NULL,
    [Opening Balance] [money] NULL,
    [Monthly Repayment] [money] NULL,
    [Interest] [money] NULL,
    [Capital] [money] NULL
) ON [PRIMARY]

GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO

Answer 1

Because your interest rate is not consistent and you cannot therefore use an interest rate calculation, you will not get any more efficient than going row by row.

Whilst not the greatest performance-wise, you will see a more efficient execution utilising a Recursive Common Table Expression (CTE), that joins to itself and checks the one row within the derived table with the next one incoming, which in your case is the next month you need to calculate the values for.

Because this row already exists in full within the CTE, you can reference the calculated values as you would in a cursor:

declare @t table(Account nvarchar(5),[Month] date null,[Rate Charge] float null,[Opening Balance] money null,[Monthly Repayment] money null);
insert into @t (Account, [Month], [Rate Charge], [Opening Balance], [Monthly Repayment]) values
 (N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000),(N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800),(N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800)
,(N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000),(N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600),(N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600)
;

with d as
(
    select Account
          ,[Month]
          ,[Rate Charge]
          ,[Opening Balance]
          ,[Monthly Repayment]
          ,row_number() over (order by Account,[Month]) as rn
    from @t
)
,c as
(
    -- Start with just the first row from your source data:
    select top 1 Account
                ,[Month]
                ,[Rate Charge]
                ,cast([Opening Balance] as decimal(12,2)) as [Opening Balance]
                ,[Monthly Repayment]
                ,[Opening Balance] * [Rate Charge] as Interest
                ,[Monthly Repayment] - ([Opening Balance] * [Rate Charge]) as Capital
                ,rn
    from d
    order by [Month]

    union all    -- Then add on the next row until the end of the dataset is reached:

    select d.Account
          ,d.[Month]
          ,d.[Rate Charge]
          ,case when c.Account <> d.Account     -- When we hit a new account, restart the calculations:
                then cast(d.[Opening Balance] as decimal(12,2))
                else cast(c.[Opening Balance] - c.Capital as decimal(12,2))
                end as [Opening Balance]
          ,d.[Monthly Repayment]
          ,case when c.Account <> d.Account
                then d.[Opening Balance] * d.[Rate Charge]
                else (c.[Opening Balance] - c.Capital) * d.[Rate Charge]
                end as Interest
          ,case when c.Account <> d.Account
                then d.[Monthly Repayment] - (d.[Opening Balance] * d.[Rate Charge])
                else d.[Monthly Repayment] - ((c.[Opening Balance] - c.Capital) * d.[Rate Charge])
                end as Capital
          ,d.rn
    from c
        join d
            on c.rn = d.rn-1
)
select Account
      ,[Month]
      ,[Rate Charge]
      ,[Opening Balance]
      ,[Monthly Repayment]
      ,Interest
      ,Capital
from c
order by rn
option (maxrecursion 0)    -- No matter how big the dataset is.

Output:

+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| Account |   Month    | Rate Charge | Opening Balance | Monthly Repayment |     Interest     |     Capital      |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| A       | 2005-06-30 |    0.014419 |         2000.00 |              0.00 |           28.838 |          -28.838 |
| A       | 2005-07-31 |      0.0149 |         2028.84 |             35.88 |       30.2296862 |        5.6503138 |
| A       | 2005-08-31 |      0.0149 |         2023.19 |             35.88 |   30.14552632438 | 5.73447367562001 |
| A       | 2005-09-30 |    0.014419 |         2017.46 |             35.88 | 29.0896912340712 | 6.79030876592877 |
| A       | 2005-10-31 |      0.0149 |         2010.67 |             35.88 | 29.9589783993877 | 5.92102160061234 |
| A       | 2005-11-30 |    0.014419 |         2004.75 |             35.88 | 28.9064755195408 | 6.97352448045923 |
| A       | 2005-12-31 |      0.0149 |         1997.78 |             35.88 | 29.7668694852412 | 6.11313051475884 |
| A       | 2006-01-31 |      0.0149 |         1991.67 |             35.88 | 29.6758363553301 | 6.20416364466991 |
| A       | 2006-02-28 |    0.013458 |         1985.47 |             35.88 |   26.72039922567 | 9.15960077432997 |
| A       | 2006-03-31 |      0.0149 |         1976.31 |             35.88 | 29.4470249484625 | 6.43297505153752 |
| A       | 2006-04-30 |    0.014419 |         1969.88 |             35.88 | 28.4036568227319 | 7.47634317726812 |
| A       | 2006-05-31 |      0.0149 |         1962.40 |             35.88 | 29.2398144866587 | 6.64018551334129 |
| A       | 2006-06-30 |    0.014419 |         1955.76 |             35.88 | 28.2001007650831 | 7.67989923491687 |
| A       | 2006-07-31 |      0.0149 |         1948.08 |             35.88 | 29.0263935013997 | 6.85360649860026 |
| A       | 2006-08-31 |      0.0149 |         1941.23 |             35.88 | 28.9242732631709 | 6.95572673682915 |
| A       | 2006-09-30 |    0.014419 |         1934.27 |             35.88 | 27.8903007461817 | 7.98969925381834 |
| A       | 2006-10-31 |      0.0149 |         1926.28 |             35.88 | 28.7015764811181 |  7.1784235188819 |
| A       | 2006-11-30 |    0.014419 |         1919.10 |             35.88 | 27.6715256312812 | 8.20847436871876 |
| A       | 2006-12-31 |      0.0149 |         1910.89 |             35.88 | 28.4722837319061 | 7.40771626809391 |
| A       | 2007-01-31 |      0.0149 |         1903.48 |             35.88 | 28.3618860276054 |  7.5181139723946 |
| B       | 2005-06-30 |    0.014419 |         4000.00 |              0.00 |           57.676 |          -57.676 |
| B       | 2005-07-31 |      0.0149 |         4057.68 |             71.76 |       60.4593724 |       11.3006276 |
| B       | 2005-08-31 |      0.0149 |         4046.38 |             71.76 |   60.29105264876 |   11.46894735124 |
| B       | 2005-09-30 |    0.014419 |         4034.91 |             71.76 | 58.1793824681425 | 13.5806175318575 |
| B       | 2005-10-31 |      0.0149 |         4021.33 |             71.76 | 59.9178077987753 | 11.8421922012247 |
| B       | 2005-11-30 |    0.014419 |         4009.49 |             71.76 | 57.8128047006505 | 13.9471952993495 |
| B       | 2005-12-31 |      0.0149 |         3995.54 |             71.76 | 59.5335877900397 | 12.2264122099603 |
| B       | 2006-01-31 |      0.0149 |         3983.31 |             71.76 | 59.3513724580716 | 12.4086275419284 |
| B       | 2006-02-28 |    0.013458 |         3970.90 |             71.76 | 53.4403906705407 | 18.3196093294593 |
| B       | 2006-03-31 |      0.0149 |         3952.58 |             71.76 | 58.8934478209911 | 12.8665521790089 |
| B       | 2006-04-30 |    0.014419 |         3939.71 |             71.76 | 56.8067282041309 | 14.9532717958691 |
| B       | 2006-05-31 |      0.0149 |         3924.76 |             71.76 | 58.4788752502416 | 13.2811247497585 |
| B       | 2006-06-30 |    0.014419 |         3911.48 |             71.76 | 56.3996139022332 | 15.3603860977668 |
| B       | 2006-07-31 |      0.0149 |         3896.12 |             71.76 | 58.0521822471433 | 13.7078177528567 |
| B       | 2006-08-31 |      0.0149 |         3882.41 |             71.76 | 57.8479415154824 | 13.9120584845176 |
| B       | 2006-09-30 |    0.014419 |         3868.50 |             71.76 | 55.7798718187117 | 15.9801281812883 |
| B       | 2006-10-31 |      0.0149 |         3852.52 |             71.76 | 57.4025460900988 | 14.3574539099012 |
| B       | 2006-11-30 |    0.014419 |         3838.16 |             71.76 | 55.3424657520731 | 16.4175342479269 |
| B       | 2006-12-31 |      0.0149 |         3821.74 |             71.76 | 56.9439627397059 | 14.8160372602941 |
| B       | 2007-01-31 |      0.0149 |         3806.92 |             71.76 | 56.7231670448216 | 15.0368329551784 |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+

Answer 2

You can try something like this, using CTE.
In the CTE construction, it use ROW_NUMBER to link each row with preceding one and calculate the various amount as you described.

    WITH  X AS (SELECT [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Monthly Repayment]-[Opening Balance]* [Rate Charge] AS [Capital]
                       , ROW_NUMBER() OVER (ORDER BY  [Month]) AS RN
                FROM [example_2]                                        
                )
    , X2 AS (SELECT [Month]
                   , [Rate Charge]
                   , [Opening Balance]
                   , [Monthly Repayment]
                   , [Interest]=[Opening Balance]*[Rate Charge]
                   , [Capital]
                   , RN
                FROM X WHERE RN=1
                UNION ALL
                SELECT X.[Month]
                   , X.[Rate Charge]
                   , CAST( XX1.[Opening Balance]-XX1.Capital AS money) AS [Opening Balance]
                   , X.[Monthly Repayment]
                   , (XX1.[Opening Balance]-XX1.Capital)*X.[Rate Charge] AS [Interest]
                      , X.[Monthly Repayment]-(XX1.[Opening Balance]-XX1.Capital)*X.[Rate Charge] AS [Capital]
                   , X.RN               
                FROM X 
                INNER JOIN X2 XX1 ON X.RN=XX1.RN+1
                )
   SELECT [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]
   FROM X2;

Output:

Month      Rate Charge            Opening Balance       Monthly Repayment     Interest               Capital
---------- ---------------------- --------------------- --------------------- ---------------------- ----------------------
2005-06-30 0.014419               2000.00               0.00                  28.838                 -28.838
2005-07-31 0.0149                 2028.838              35.88                 30.2296862             5.6503138
2005-08-31 0.0149                 2023.1877             35.88                 30.14549652438         5.73450347562
2005-09-30 0.014419               2017.4532             35.88                 29.089657640685        6.79034235931497
2005-10-31 0.0149                 2010.6629             35.88                 29.9588765788462       5.9211234211538
2005-11-30 0.014419               2004.7418             35.88                 28.9063716764904       6.97362832350962
2005-12-31 0.0149                 1997.7682             35.88                 29.7667457579797       6.1132542420203
2006-01-31 0.0149                 1991.6549             35.88                 29.6756586917939       6.2043413082061
2006-02-28 0.013458               1985.4506             35.88                 26.7201936188742       9.15980638112584
2006-03-31 0.0149                 1976.2908             35.88                 29.4467328249212       6.43326717507878
2006-04-30 0.014419               1969.8575             35.88                 28.4033757658025       7.47662423419746
2006-05-31 0.0149                 1962.3809             35.88                 29.2394750489105       6.64052495108955
2006-06-30 0.014419               1955.7404             35.88                 28.1998204678302       7.68017953216976
2006-07-31 0.0149                 1948.0602             35.88                 29.0260972849707       6.85390271502934
2006-08-31 0.0149                 1941.2063             35.88                 28.9239738295461       6.95602617045394
2006-09-30 0.014419               1934.2503             35.88                 27.8899546983482       7.99004530165178
2006-10-31 0.0149                 1926.2603             35.88                 28.7012777950054       7.17872220499462
2006-11-30 0.014419               1919.0816             35.88                 27.6712372702262       8.20876272977382
2006-12-31 0.0149                 1910.8728             35.88                 28.4720052753264       7.40799472467363
2007-01-31 0.0149                 1903.4648             35.88                 28.3616255986024       7.51837440139764