Valuing without accrued interest (UB)
Jan 23, 2024 18:10:29 GMT
via mobile
Ace, redpete, and 2 more like this
Post by trium on Jan 23, 2024 18:10:29 GMT
On another board, Ace referred to UB's inclusion of accrued interest in portfolio values saying he didn't know an easy way to exclude it (though he said he hadn't tried too hard). Maybe this might help?
Sign in to UB, click "Transactions" then download the CSV (warning - there's a fair wait)
Open the CSV in a spreadsheet program.
Keeping the headings in Row 1, reverse the rest of the sheet so that the oldest entries come first.
Insert a new row before Row 2 and set the new cells G2, H2 and I2 to zero.
Set G3 to "=G2+D3" and make the heading for Column G "Cash"
Set H3 to "=H2-IF(D3<0,D3,F3) and make the heading "On Loan". NOTE: If using Open Office replace the commas with semicolons
Set I3 to "=I2+E3" and head it "Interest Paid"
Use the drag handle to extend the formulae in G3 to I3 all the way down.
The value of your portfolio (without accrued interest) on any given date is the sum of the figures read from Columns G and H at the end of that day. Column I is not used in the calculation and can be omitted, but I like to have it.
The above assumes that you are starting from zero, ie going back to when you opened the account, but you can use different starting balances in Row 2 if for example you have last year's figures and you want to start a new sheet for this year.
HTH
Sign in to UB, click "Transactions" then download the CSV (warning - there's a fair wait)
Open the CSV in a spreadsheet program.
Keeping the headings in Row 1, reverse the rest of the sheet so that the oldest entries come first.
Insert a new row before Row 2 and set the new cells G2, H2 and I2 to zero.
Set G3 to "=G2+D3" and make the heading for Column G "Cash"
Set H3 to "=H2-IF(D3<0,D3,F3) and make the heading "On Loan". NOTE: If using Open Office replace the commas with semicolons
Set I3 to "=I2+E3" and head it "Interest Paid"
Use the drag handle to extend the formulae in G3 to I3 all the way down.
The value of your portfolio (without accrued interest) on any given date is the sum of the figures read from Columns G and H at the end of that day. Column I is not used in the calculation and can be omitted, but I like to have it.
The above assumes that you are starting from zero, ie going back to when you opened the account, but you can use different starting balances in Row 2 if for example you have last year's figures and you want to start a new sheet for this year.
HTH
