Best way to convert an Excel model to MATLAB

I wish to use MATLAB rather than the following Excel worksheet

The following MATLAB code acheives this

May I ask is there a better approach than the above, which is sort of like using visual basic in Excel?

For example, is there a more "object orientated" approach where the formulas representing each excel column, e.g. "opening(t) = closing(t-1)", are specified in their own separate file or something? Or, perhaps there is a MATLAB toolbox suitable for this type of model?

Cheers

[darova]

What are you trying to achieve? The question is not quite clear

[Tim Berry]

Thanks Darova. I hope to bring into MATLAB a much more complicated version of the sample Excel worksheet above, and then hope that MATLAB will allow a much faster calculation when I repeat the calculation thousands of times with different inputs. If a more complicated worksheet has say 60 columns each with a formula, then the simple script approach above would become difficult to work with. It might be nicer if each formula were its own sort of object. Possibly the Excel formulas could be made into MATLAB functions, but each formula may depended on other formulas (and sometimes another formula calcuated in the previous row e.g. at t-1.)

Answer 1

[Sameer]

Hi Tim,

For a more complex model like the one you're describing, where you have many columns with interdependent formulas, an object-oriented approach in MATLAB could indeed make your code more manageable and modular. This would involve defining classes that encapsulate the behaviour of your financial model, with methods representing the calculations for each column.

Here's an example of how you might structure such a model using MATLAB's object-oriented programming features:

classdef FinancialModel
    properties
        Opening
        Growth
        Contribution
        Closing
        GrowthRate
        ContributionAmount
        NumPeriods
    end
    
    methods
        function obj = FinancialModel(growthRate, contributionAmount, numPeriods)
            % Constructor to set up initial values and allocate arrays
            obj.GrowthRate = growthRate;
            obj.ContributionAmount = contributionAmount;
            obj.NumPeriods = numPeriods;
            obj.Opening = zeros(1, numPeriods);
            obj.Growth = zeros(1, numPeriods);
            obj.Contribution = zeros(1, numPeriods);
            obj.Closing = zeros(1, numPeriods);
        end
        
        function obj = calculatePeriod(obj, t)
            % Calculate values for a single period
            if t == 1
                obj.Opening(t) = 0;
            else
                obj.Opening(t) = obj.Closing(t-1);
            end
            obj.Growth(t) = obj.Opening(t) * obj.GrowthRate;
            obj.Contribution(t) = obj.ContributionAmount;
            obj.Closing(t) = obj.Opening(t) + obj.Growth(t) + obj.Contribution(t);
        end
        
        function obj = runModel(obj)
            % Run the model for all periods
            for t = 1:obj.NumPeriods
                obj = obj.calculatePeriod(t);
            end
        end
    end
end

To use this class, you would create an instance of “FinancialModel” and then call its methods to perform the calculations:

growthRate = 0.05;
contributionAmount = 100;
numPeriods = 10;

model = FinancialModel(growthRate, contributionAmount, numPeriods);
model = model.runModel();

% Now you can access the results
disp(model.Closing);

This modular approach allows you to encapsulate the logic for each calculation in its own method, which makes your code easier to read, maintain, and test. If you have different types of calculations or scenarios, you could extend this class or create different classes as needed.

For financial modelling specifically, MATLAB offers the Financial Toolbox, which provides functions for mathematical modelling and statistical analysis of financial data. This toolbox have pre-built functions that can simplify your modelling process.

www.mathworks.com/products/finance.html

I hope this helps!

Sameer