Spreadsheet formulas for personal finance
1–10 of 123 posts
Re: Spreadsheet formulas for personal finance
#2I love mid.
Sometimes you don't want the first few characters, or the last few, you just really want the ones in the middle.
Re: Spreadsheet formulas for personal finance
#3[deleted]
Re: Spreadsheet formulas for personal finance
#4Interesting 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 relevant pricing APIs to auto-update position values.
Re: Spreadsheet formulas for personal finance
#5Interesting 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 income, so to the extent you are trying to capture the diversification benefits of allocating across different risk buckets, you may want to scale your exposure according to volatility[1].
There is a modeling challenge here, of course, because asset classes will never be independent risks, but I'd prefer something directionally indicative rather than econometrically optimal.
[0] https://en.wikipedia.org/wiki/Risk_parity [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...
Re: Spreadsheet formulas for personal finance
#6Hey - creator of BudgetSheet here (https://www.budgetsheet.net/ ).
These formulas are great! I am currently working on adding more charts, graphs, and formulas to my product. These formulas and handy to know about for a net worth sheet. Thanks for sharing!
Re: Spreadsheet formulas for personal finance
#7Interesting 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…
That'd be a great addition. I plan to update this with historical charting and tracking of position values over time. I'll see if I can find a way to add risk parity. Makes a lot of sense.
Re: Spreadsheet formulas for personal finance
#8Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.
Re: Spreadsheet formulas for personal finance
#9Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.
I think you could probably do this in a single formula with SUMPRODUCT, but right now I just have a table that uses MIN(0, X) and MAX(0, Y) tricks to calculate it.
Re: Spreadsheet formulas for personal finance
#10I 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 year."
Schwab is terrible at this. "You gained 5% today!" No, I didn't, I deposited $1,000 into my brokerage account.