On Sep 23, 4:21*am, JP wrote:
I'm curious, what kind of code does the spreadsheet have that couldn't
be found online somewhere?
I'm a financial planner. The spreadsheet started life as a time
saving rough workbook for financial plans. It had simple
functionality like you enter the client's date of birth and it
calculates their age and looks that age up in an actuarial table to
get their life expectancy, pension age etc.
Then much like spreadsheets often do it grew more complex. I learned
to record macros and then figured out how to edit the code of those
macros to insert program logic. I made a unique and highly efficient
asset allocation tool, risk analyser and portfolio creator.
It also featured a simple retirement planning spreadsheet, but I kept
adding new stuff to it so in time it came to more and more
realistically model the tax system including social security and
superannuation pensions (Australian equivalent of 401k). What I'm
particularly proud of is that now it enables the user to select their
savings and spending priorities, i.e. they could number from 1 to 32
(or 5 to 10, it doesn't require all the numbers to be used and only
cares about the order) how they want to invest. 1 could be "if you
have spare cash, pay off the mortgage". 2 could be "if there is
anything left after paying the mortgage, contribute the surplus to
superannuation up to the amount required to get taxable income down to
a target threshold" etc. Spending is similar, "If there is a
shortfall, withdraw from the bank:1" "If there isn't enough money in
the bank, increase superannuation pensions:2", "if there isn't enough
in the bank or pensions aren't available, do a mortgage redraw: 3".
With more than 30 investment priorities and more than 10 spending
options (configurable for each year individually, over a projection
which goes until the client's 100th birthday) all the fiddly number
crunching of a financial planning projection gets reduced to first
entering the starting values (portfolio size, existing debts, income,
setting up relationships between clients and trusts, companies etc)
and then just setting priorities, then hitting a button which sends a
macro running for a minute... and then it spits out decades of tax-
realistic projections. This enables the financial planner using it to
quickly compare strategies, if he wants to see if its better to pay
off the mortgage first or fully fund superannuation they just flip the
priorities numbers around and rerun the macro.
In terms of algorithms, it isn't that sophisticated. The priorities
macro is a loop within a loop passing the value of the spending or
saving priority out in order, running subs which are mostly just using
a solver "make surplus equal to zero by changing cash allocated to
priority". Its just one of those spreadsheets that shows the power of
a handful of VBA tools when applied in the right way. I'm not worried
about some VBA guru looking at my code and being impressed with my
deft use of offset cell referencing and solver, I'm more concerned
about financial planners getting their hands on it without paying.
Numerous planners have seen it as have a few software companies who
make financial planning software. They all agree my beast of a
spreadsheet (which prior to converting to Office 2007 format was
almost 12 megabytes in size!) is about as sophisticated and as
efficient as financial planning software anywhere. Inferior
commercial products are leased to planners for $10K per year, and
there are tens of thousands of planners. All in all, it wouldn't be a
bad little software niche if you can come up with a killer app.
So I've decided to commercialise it, and a programmer mate has
convinced me that online apps are the way to go so we're working on an
Ajax version. But in the mean time, I've got a number of planners
offering me hundreds of dollars a month to use the spreadsheet.
What they'll pay me should be enough to pay for most of the
development costs of creating my commercial package, which I know will
do very well commercially because of its superior combination of power
and ease of use and because I already have a lot of people asking me
when I'm going to commercialise it.
But obviously I'll want to copy protect it first. I am aware of how
insecure Excel VBA is, having cracked open more than a few
spreadsheets in my time to see how they work. Nevertheless, now that
I'm on the other side I want to protect my IP from being emailed to
every financial planner in the country and freely used. :)
Travis
www.travismorien.com