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

101–110 of 123 posts

Re: Spreadsheet formulas for personal finance

#101

Post by SamReidHughes »

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…

Thanks, I wanted that and just used it.

I then realized you could also manually calculate XIRR using Goal Seek. Suppose your dates are in D2:D100 and values in V2:V100. Add a cell, let's say I1, to hold an interest rate value, and another column W with formulas defined as follows (filled downward from W2):

    =$V2*(1+$I$1)^(($D$100-$D2)/365)
Then make a cell J1 with formula =SUM(W2:W100).

Run Goal Seek to find the value of I1 which sets J1 to the value 0. This will give you XIRR. (At least for non-leap years. I'm not sure how leap year logic works.)

Re: Spreadsheet formulas for personal finance

#102

Post by Alupis »

Aeolun wrote: post #93
Alupis wrote: post #43

Earlier quoted context omitted.

It is not somehow patriotic to pay more taxes than one legally owes. Overpaying taxes (what a lot of people unintentionally do that don't have resources to navigate every nook and cranny of the labyrinthian tax code) accomplishes nothing but make the individual more poor than they already were. Minimizing overpayment is the best possible thing an individual can do.

There are governments that I happily overpay to because they’re actually a net good to the people. Others, not so much. I very much try to follow thr spirit of the law though, not the letter, both in laws I ignore and adhere to.

Unless your extra generous contributions are in the 10's of thousands, hundreds of thousands, or millions of dollars per year (depending on which government you're referring to - local, state, fed), I can assure you, whatever money you send to whatever government is not significant enough to make any difference. You're far better off sending that money directly to vetted non-profits that do the things you think your area needs more of.

Not to mention, governments like the US fed will just poof new money out of thin air if they can't afford something anyway.

And... when it comes to tax law, you had better follow the letter not the spirit, otherwise you risk an unpleasant visit from your local tax auditor. Your interpretation of the spirit of the law won't get you out of their crosshairs.

Re: Spreadsheet formulas for personal finance

#103

Post by shashank »

lbotos wrote: post #84
lbotos wrote: post #82

Earlier quoted context omitted.

Decent price and looks reasonably featureful. Good job! Question: Who are you using for Bank sync? Yodlee? Plaid? Something else? I'm a mint ->> Personal Capital convert who both use Yodlee, and I'm curious who you use.

ah, found it here for others who might be interested: https://www.buxfer.com/help/security Yodlee and SaltEdge

Yes that’s right. Most of US bank coverage is through Yodlee. We got started with them and have stuck with them so far. Tinkered with Plaid a little but Yodlee’s per-user pricing works better for us than Plaid’s per-account pricing (power users tend to have lots of accounts). Haven’t revisited this in a while so things might have changed but it’s a pain to switch data providers so the benefit has to be very tangible.

For some reason, many of our users find our bank sync to be more reliable even though everyone uses the same providers underneath. I routinely conduct user interviews and this is a common piece of feedback. I like to think it’s a side effect of us building bank sync in-house in the past (which is a royal PITA to build and maintain). But that somehow let us build a more robust system on top of the same (unreliable) bank data aggregators.

Anyhow feels like I’m rambling so I’ll stop now :)

Re: Spreadsheet formulas for personal finance

#104

Post by hiq »

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…

I was trying to figure what this XIRR does: XIRR is the Excel function computing the IRR, which I didn't know about: https://www.investopedia.com/terms/i/irr.asp

But this is actually equivalent to what I knew as money weighted returns: https://www.investopedia.com/terms/m/money-weighted-return.a...

Re: Spreadsheet formulas for personal finance

#105

Post by martian42 »

scubakid wrote: post #68

Wow this is like a flashback to my own experiments in Excel, prior to going full mad-scientist for the past 6 months and creating a web app for FI/FIRE planning called ProjectiFi: https://projectifi.io/ I had been using similar functions in parts of my spreadsheet, but as I kept adding more and more nuance and flexibility to the life planning and projection aspects, eventually the formulas just reached such a critica…

Hey, could you add support for cryptocurrency in investments? So that I could enter how much I have in cryptocurrency instead of $USD, and it would automatically calculate it to $.

Re: Spreadsheet formulas for personal finance

#106

Post by boulos »

shepherdjerred wrote: post #36
asdff wrote: post #28

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 accoun…

Excel[0] has this functionality built in via Plaid[1]. There are other solutions like YNAB[2] or Wealthfront[3]. I agree with you though. I have a nice python program for understanding my finances, but I still have to manually update bank/credit card/loan balances. [0] https://plaid.com/blog/microsoft-announcement/ [1] https://plaid.com/ [2] https://www.youneedabudget.com/ [3] https://www.wealthfront.com/

If you’re already writing Python, check out ofxtools [1]. Pretty easy to use, and csingley has done a great job modeling the OFX specs, complete with type hints!

[1] https://github.com/csingley/ofxtools

Re: Spreadsheet formulas for personal finance

#107

Post by shapefrog »

xorfish wrote: post #27
settrans wrote: post #5

Earlier quoted context omitted.

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...

I would second this - I have no fancy papers or citations etc but eventually risk parity will blow up the world.

There are broadly 2 regimes that dictate volatility and correlation, normal and shit-hitting-the-fan. Risk parity models skew heavily towards the everyday, when prices tick up / down by small amounts, and diversification exists.

On adverse market wide event, there is (generally) no diversification, and leveraged portfolios in particular can face significant losses.

There is no silver bullet, but portfolio wide value at risk, i.e. what the outcome on the day/week/month on any given day in the last 5 years (or more) had I held these exact same positions is as good a measure as any. The distribution of outcomes being something worth understanding and tuning risk to.

Re: Spreadsheet formulas for personal finance

#108

Post by gizdan »

scubakid wrote: post #68

Wow this is like a flashback to my own experiments in Excel, prior to going full mad-scientist for the past 6 months and creating a web app for FI/FIRE planning called ProjectiFi: https://projectifi.io/ I had been using similar functions in parts of my spreadsheet, but as I kept adding more and more nuance and flexibility to the life planning and projection aspects, eventually the formulas just reached such a critica…

Are you planning to add a self-hosted option? My one thing about all these finance SaaS is that I'm sure part of their business model is selling your data, even if anonymised. I'd be happy to pay a one-off fee and self-host it or something.

Re: Spreadsheet formulas for personal finance

#109

Post by marvin »

martian42 wrote: post #105
scubakid wrote: post #68

Wow this is like a flashback to my own experiments in Excel, prior to going full mad-scientist for the past 6 months and creating a web app for FI/FIRE planning called ProjectiFi: https://projectifi.io/ I had been using similar functions in parts of my spreadsheet, but as I kept adding more and more nuance and flexibility to the life planning and projection aspects, eventually the formulas just reached such a critica…

Hey, could you add support for cryptocurrency in investments? So that I could enter how much I have in cryptocurrency instead of $USD, and it would automatically calculate it to $.

Wouldn't that be a real crapshoot anyway, if you're thinking in the timeframes relevant for financial independence? Surely you're not expecting your crypto portfolio to predictably appreciate by 6-8% per year over a 10 year timeframe with large variance.

The potential for going much higher and going down by 90% is in a different league.

Re: Spreadsheet formulas for personal finance

#110

Post by [deleted] »

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…

[deleted]
Post reply on HN