Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Spreadsheet formulas for personal finance

bou.ke

Post reply on HN
Print view View original

21–30 of 123 posts

Re: Spreadsheet formulas for personal finance

#21

Post by bouk »

basseq wrote: post #20
jldugger wrote: post #8

Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.

Because I like a challenge: =SUMPRODUCT( (bracket_min bracket_max)*(bracket_max-bracket_min)) ) *bracket_rate ) Where income is your income, bracket_min is the range of bracket minimums, bracket_max is the range of bracket maximums, and bracket_rate is the range of bracket tax rates. Demo on Google Sheets: https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7r...

Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...

Re: Spreadsheet formulas for personal finance

#22

Post by basseq »

kccqzy wrote: post #19
basseq wrote: post #10

I like XIRR to get an actual sense of what my annualized portfolio performance looks like. Pretty much every bank, brokerage, or financial software I've used is "dumb" and will a) count deposits as growth , b) show total lifetime growth, and/or c) ignore deposits. XIRR allows me to better benchmark portfolio performance by accounting for when I deposit (or withdraw) money so I can clearly say, "I'm earning X% per yea…

HealthEquity, where I keep my HSA account, manages to get this right. They use modified Dietz return which is easier to calculate than the IRR but remarkably similar. I calculate the modified Dietz return for my other accounts too. It's easy enough that unlike IRR you don't need the function to be built in to the spreadsheet software to calculate it.

This is great! Thanks for the info.

Re: Spreadsheet formulas for personal finance

#23

Post by conductr »

captainill wrote: post #14

Does anyone have a good resource for learning to write beginner through advanced formulas?

Not to be snarky but, Just start. Pretty much every excel user is self taught. You can google for the name of the formula that is helpful for your use just like you would programming.

Most of what separates a power user from an average joe is having an approach to the problem and laying the foundation in a way to be a “model”.

I’m seen as a power user even within my peer group of finance folks. I just have a way of laying things out and breaking up the problem from data/inputs to print ready outputs. I’m not usually doing anything fancy like using obscure formulas. But when I do, I like these;

* Use -- to convert Boolean to integer * Use index/match instead of vlookup (you can more easily insert/delete columns without breaking) * make yourself a style guide (so you know what a color means; hard value, input, etc) * avoid volatile functions. Learn what functions are volatile * get good at auditing formulas/debugging. It's really just takes experience

Re: Spreadsheet formulas for personal finance

#24

Post by JALTU »

basseq wrote: post #10

I like XIRR to get an actual sense of what my annualized portfolio performance looks like. Pretty much every bank, brokerage, or financial software I've used is "dumb" and will a) count deposits as growth , b) show total lifetime growth, and/or c) ignore deposits. XIRR allows me to better benchmark portfolio performance by accounting for when I deposit (or withdraw) money so I can clearly say, "I'm earning X% per yea…

Personal finance is simple, financial planning (forecasting/scenario planning) is hard, and building software to "teach" this to customers is vastly harder and not really in the wheelhouse of financial institutions. This isn't to defend such companies, nor to knock developers, just to point out that it's very difficult to meet people where they are on this very personal topic.

Then again, the Ux of Fidelity's site, for example, is also a great example of how not to do things, IMO.

Re: Spreadsheet formulas for personal finance

#25

Post by xorfish »

imsd wrote: post #4

Interesting formulas to take my spreadsheets up a level. For anyone interested, I made a Google Sheets template that I share with my friends. It has been well-received. The crypto section can be ignored for those not involved with that sector. If it's useful, would love to hear your feedback. https://docs.google.com/spreadsheets/d/1qYLOAjzaIIcFLFw_j-P4... Of course, much can be automated using Google Finance and rele…

Here is my sheet that is mainly used for rebalancing ETFs according to some simple rules. It is currency agnostic (base currency can be changed) and should work fine for European investors as well.

It automatically pulls MSCI market cap information and determines the allocation based on that.

Then the number of shares that you need to buy or sell is calculated based on the target allocation.

If you use Interactive Brokers, then buy and sell texts for the IBOT are also generated.

https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7...

Re: Spreadsheet formulas for personal finance

#26

Post by mattbillenstein »

Re FIRE, I built this model some time ago: https://docs.google.com/spreadsheets/d/1E6TjJyowYowexkSvNV8t...

Sorta more or less models and validates the 4% rule.

And I have another sheet to track all my investments, etc using the GOOGLEFINANCE function to update with the market.

I've also looked at using something like Plaid to import banking/credit card data, but it was all too complicated. So I now get a daily balance update via email that I scrape into my sheet using a Python script... Keeps me from needing to manually update the balances myself.

Re: Spreadsheet formulas for personal finance

#27

Post by xorfish »

settrans wrote: post #5
imsd wrote: post #4

Interesting formulas to take my spreadsheets up a level. For anyone interested, I made a Google Sheets template that I share with my friends. It has been well-received. The crypto section can be ignored for those not involved with that sector. If it's useful, would love to hear your feedback. https://docs.google.com/spreadsheets/d/1qYLOAjzaIIcFLFw_j-P4... Of course, much can be automated using Google Finance and rele…

This looks great; it nearly identical to a sheet I developed for my personal use. The biggest improvement I'd like to make to mine is to implement some approximated form of risk parity[0]. That is, instead of comparing nominal allocations, to compare weighted risk allocations by asset class. This is useful because (for example) equities will contribute significantly more volatility to your portfolio than, say, fixed…

I would caution against using risk parity as it assumes that you know the volatility and correlation of different asset classes.

Look at the Figure 1 of this paper:

https://www.casact.org/sites/default/files/old/01pcas_scheel...

Re: Spreadsheet formulas for personal finance

#28

Post by asdff »

I wish it was easier to actually pull information from various banks and accounts. For all of mine, I have to log in to the website and click around some 2006 era website to set a range or dates or something before I can generate a CSV file. It's a ton of friction just to get a look at my financials. I wish I could always have access to updated CSV files without having to spend all this time for each and every account. The only alternative is paid services like mint, which I don't want. I just want my raw data so I can roll my own and come up with my own ways to manage my finances.

Re: Spreadsheet formulas for personal finance

#30

Post by whateveracct »

jldugger wrote: post #8

Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.

Not in spreadsheets, but there's fun to be had here in Haskell-land: https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Ta...

I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh

Post reply on HN