Creating columns and loading data

Question

I want to do some personal finance analysis for myself and I really want to do it in SQL Server in a stored procedure (vs purely Excel) for the sake of understanding the programming elements of SQL better. I have several ideas in mind for what variables I want to pass through, but I need some advice for the structure. Perhaps I'm overthinking it, but I'm confused with how to create an initial column that has the values from the user's current age to 60 (see the example below). So the sproc would take in one variable, which is the user's age. After that portion of the table is generated, then I want to include columns like Savings and Retirement where I would have some underlying equations for how these accounts would grow based off age. I don't really know where to start and I really appreciate any advice. What commands should I use for this? Temp tables? While loops?

Years  Age   Savings   Retirement
1      21     10,000         6,000
2      22     15,000        13,000
3      23     21,000        20,000
4      24     28,000        30,000
5      25     36,000        42,000
...    ...       ...           ...
40     60    300,000       500,000

First of all +1 for deciding to use Stored Procedures. You are on the right track. This is how I learnt SQLServer except I used sports stats. IMHO the single most important thing in SQL Server is the SQL language and the ability to think in terms of sets instead of rows. Once you get the hang of it everything else kinda starts to become simpler. But for your situation I suggest you pick some common Finance calculations that you want to do and start to play with it and implement it in SQL Server. — objectNotFound, Jul 22 '16 at 23:14
Most of what you want to achieve can be done using Select statements unless the situation is complex and needs constructs and features other than select. I suggest you first try to understand the concept of joins and implement some basic scenarios. There is plenty of help available. Good luck. — objectNotFound, Jul 22 '16 at 23:18
Hi, thank you for the feedback. I'm mainly trying to create the columns Years and Age, and then in a select statement I will perform the finance calculations. So it would look something like: SELECT Years, Age, @Income * 1.05^[Years] AS Savings FROM [YearsAgeTable]. I want the [YearsAgeTable] to be dynamic though, so that it begins at the Age variable. — Walker, Jul 22 '16 at 23:23
You are on right track. So if your question is how to make it dynamic ... then yes you need a table that will hold (atleast) the necessary information. To create a table (not temp table ) you need a database to play with. I assume you have one. If not let me know. The syntax for creating a table is very straightforward and easy to find (google ). — objectNotFound, Jul 22 '16 at 23:35
I have a database set up, but I don't understand why I'd need an actual table. Why not just use a temp table or recursive CTE to generate the list of numbers? — Walker, Jul 22 '16 at 23:37
If you use a Temp Table you will lose your Data after you close your session. I would imagine you would need this data to be around for a while till you are done learning ... Right ? And if you understand CTE's then you are not a beginner ... lol. — objectNotFound, Jul 22 '16 at 23:42
The answers to [this](http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) question should help you with generating a numbers table or numbers on-the-fly. — HABO, Jul 23 '16 at 02:30

score 0 · Answer 1 · answered Jul 22 '16 at 22:52

Well,

This question is pretty vague and opinion based, but I don't have the reputation to deal with such things :P.

Sounds like an awesome project.

I recommend creating a computation table that doesn't deal with real dollars, but is instead a series of columns with rows of factors that will multiply against your input data. Say your input is age and savings_at_certain_age... Well you could then have those numbers programmatically multiply against the correct couple fields from your computation table(s) to produce your output...

You're going to need to write a stored procedure - which is an entirely different topic - but is the appropriate functionality for your problem. Look in to temp-variables, temp-tables, and stored-procedures as you pursue your problem.

score 0 · Accepted Answer · answered Jul 26 '16 at 16:31

Okay, I spent some time working on this and found a solution that works for me. I still have some work to do to bring in compounding interest.

CREATE PROCEDURE [dbo].[personalfinanceTest]
    @Input_AgeCurrent               INT
    ,@Input_AgeRetire               INT
    ,@Input_Income                  DECIMAL(10,2)
    ,@Input_IncomeIncreasePct       DECIMAL(10,6)
    --@PostTaxSalarySavingsPct decimal(10,2),
    ----Investment
    ,@Input_401k_PersonalPct        DECIMAL(10,2)
    ,@Input_401k_EmployerPct        DECIMAL(10,2)
    ,@Input_IRA_TradAmt             DECIMAL(10,2)
    ,@Input_IRA_RothAmt             DECIMAL(10,2)
    ----Market
    --@Inflation decimal(10,2),
    --@SavingsInterest decimal(10,2)
AS


CREATE TABLE #Table
    ([Age]                          INT             NOT NULL
     ,[Years]                       INT             NULL
     ,[Income]                      DECIMAL(10,2)   NULL
     ,[401k Personal]               DECIMAL(10,2)   NULL
     ,[IRA Traditional]             DECIMAL(10,2)   NULL
     ,[Taxable Income]              DECIMAL(10,2)   NULL
     ,[Tax]                         DECIMAL(10,2)   NULL
     ,[PostTax Income]              DECIMAL(10,2)   NULL
     ,[Savings]                     DECIMAL(10,2)   NULL
     ,[401k Employer]               DECIMAL(10,2)   NULL
     ,[401k Total]                  DECIMAL(10,2)   NULL
     ,[IRA Roth]                    DECIMAL(10,2)   NULL
     ,[IRA Total]                   DECIMAL(10,2)   NULL
     ,[Cumulative Savings]          DECIMAL(10,2)   NULL
     ,[Cumulative Retirement]       DECIMAL(10,2)   NULL
    )

DECLARE @age                        INT             = @Input_AgeCurrent
DECLARE @prev_savings               DECIMAL(10,2)   = 0
DECLARE @prev_retirement            DECIMAL(10,2)   = 0


WHILE @age <= @Input_AgeRetire
    BEGIN
        DECLARE @years              INT             = @age - @Input_AgeCurrent
        DECLARE @income             DECIMAL(10,2)   = @Input_Income * POWER(@Input_IncomeIncreasePct + 1,@years)
        DECLARE @401k_personal      DECIMAL(10,2)   = @Input_401k_PersonalPct * @income
        DECLARE @401k_employer      DECIMAL(10,2)   = @Input_401k_EmployerPct * @income
        DECLARE @tax                DECIMAL(10,2)   = dbo.calculateTax(@income - @401k_personal)
        DECLARE @IRA_trad           DECIMAL(10,2)   = @Input_IRA_TradAmt
        DECLARE @IRA_roth           DECIMAL(10,2)   = @Input_IRA_RothAmt

        DECLARE @income_taxable     DECIMAL(10,2)   = @income - @401k_personal
        DECLARE @income_posttax     DECIMAL(10,2)   = @income - @tax
        DECLARE @401kTotal          DECIMAL(10,2)   = @401k_personal + @401k_employer
        DECLARE @IRATotal           DECIMAL(10,2)   = @IRA_trad + @IRA_roth

        DECLARE @retirement         DECIMAL(10,2)   = @401kTotal + @IRATotal
        DECLARE @savings            DECIMAL(10,2)   = @income_posttax - @retirement

        INSERT INTO #Table([Age]
                    ,[Years]
                    ,[Income]
                    ,[401k Personal]
                    ,[IRA Traditional]
                    ,[Taxable Income]
                    ,[Tax]
                    ,[PostTax Income]
                    ,[Savings]
                    ,[401k Employer]
                    ,[401k Total]
                    ,[IRA Roth]
                    ,[IRA Total]
                    ,[Cumulative Savings]
                    ,[Cumulative Retirement])
        SELECT @age
            ,@years
            ,@income
            ,@401k_personal
            ,@IRA_trad
            ,@income_taxable
            ,@tax
            ,@income_posttax
            ,@savings
            ,@401k_employer
            ,@401kTotal
            ,@IRA_roth
            ,@IRATotal
            ,@prev_savings + @savings
            ,@prev_retirement + @retirement


        SET @age += 1
        SET @prev_savings += @savings
        SET @prev_retirement += @retirement
    END

SELECT *
FROM #Table

Additionally, I created a function for 2016 tax brackets:

CREATE FUNCTION [dbo].[calculateTax] (@salary AS money)

RETURNS money
AS

BEGIN
    DECLARE @tax money
    DECLARE @10high money = 9275
        ,@15low     money = 9276
        ,@15high    money = 37650
        ,@25low     money = 37651
        ,@25high    money = 91150
        ,@28low     money = 91151
        ,@28high    money = 190150
        ,@33low     money = 190151
        ,@33high    money = 413350
        ,@35low     money = 413351

    IF          @salary < @10high   SET @tax = @salary * 0.1
        ELSE IF @salary < @15high   SET @tax = @10high * 0.1 + (@salary - @15low)*0.15
        ELSE IF @salary < @25high   SET @tax = @10high * 0.1 + (@15high - @15low)*0.15 + (@salary - @25low)*0.25
        ELSE                        SET @tax = @10high * 0.1 + (@15high - @15low)*0.15 + (@25high - @25low)*0.25 + (@salary - @35low)*0.35


        RETURN @tax
    END;

Creating columns and loading data

2 Answers2