Excel gurus! I need your help

jamesbrownontheroad

Hello everyone.

I will try to keep this query as simple as possible.

I manage the private provision of home care for a relative. This is paid weekly from an easy access savings account which earns 2.0% AER paid monthly.

In turn, I pay a fixed amount every week for the carer's wage + travel costs.

Knowing the balance of the bank account today, the AER, and my fixed weekly outgoings, I want to make an Excel spreadsheet to model the balance of the account and - more importantly - when the sum will run out.

Can anyone help me design this spreadsheet?

Thanks in advance!

AlwaysLearnin

The FV function might be worth a look.  Could link (some of?) the inputs in to separate cells so that you can easily play about with them to see the impacts as required (and/or use goal seek to find the value(s) that get to your required answer)

tacpot12

So I do a similar thing with my pension, but for my purposes it's good enough to have one column per year.

You will need to decide if you want to have one column per week or one per month. My recommendation is that as most of your data is per week you have one column per week. You will need to convert the AER interest rate to a daily rate and multiple the daily interest by 7 to get the weekly interest. This won't give you an accurate balance every week, but it will be accurate enough to see when the money might run out.

I start with a row for the opening balance on the account. I then add extra rows for all of the items that cause a movement on the amount, e.g. interest being paid in, expenses being withdrawn. Then I add a final row for the closing balance that adds the items being paid in and subtracts the items being withdrawn to give me the closing balance. Then I add a formula so that the opening balance for the next period is set to be the closing balance from the previous period. Then just copy all the formulas for all the weeks.

Your spreadsheet should look something like this: 

Week Number        1         2
Opening Balance     0        = Closing Balance from Week 1
Income                   10     
Outgoings                8     
Closing Balance    = Opening Balance + Income - Outgoings (for Week 1)

2.0% AER Interest = 0.0054255% per day
The formula for this is (1 + AER_RATE) ^ (1/365)                       
you can probably ignore leap years for your purposes.

You can add a formula that wil calculate the outgoings so that these rise over time with inflation. I put the assumed inflation rate into a single cell and reference it where I need to. You can convert an annual inflation rate to the daily inflation rate using the same formula used to conver the annual interest rate to a daily date. (Don't forget to multiple the daily cost by 7 to get the weekly cost).

Alderbank

JamesRobinson48 said:

Not wishing to complicate things, but naturally you're aware that costs increase over time, typically by at least the 2.0% per annum that the account is paying. So you might consider disregarding the interest income, as it could be wholly offset by the effects of inflation. Then one could probably do without Excel altogether.

Agreed, plus you don't need a spreadsheet to work out that 2%pa is a lousy rate even for easy access.

All MSE's recommended easy access accounts pay more than double that!

https://www.moneysavingexpert.com/savings/savings-accounts-best-interest/#easyaccess

Bridlington1

Alderbank said:

JamesRobinson48 said:

Not wishing to complicate things, but naturally you're aware that costs increase over time, typically by at least the 2.0% per annum that the account is paying. So you might consider disregarding the interest income, as it could be wholly offset by the effects of inflation. Then one could probably do without Excel altogether.

Agreed, plus you don't need a spreadsheet to work out that 2%pa is a lousy rate even for easy access.

All MSE's recommended easy access accounts pay more than double that!

https://www.moneysavingexpert.com/savings/savings-accounts-best-interest/#easyaccess

Or for a more comprehensive list see moneyfacts:
https://moneyfactscompare.co.uk/savings-accounts/easy-access-savings-accounts/?quick-links-first=false&product-favorites-first=false

Note different accounts will show depending on the ``Investment amount" selected and whether you've asked it to show accounts for existing members only (play around with settings).