Checkbook Register with Automatic Round-Up to Savings Feature

[DC_BarTroN]

Hello, I am working on an Excel Spreadsheet. The Spreadsheet I have designed is for personal use to keep track of my Checking Account. Its working great for what I use it for currently, but would like to implement a feature that my bank uses called "Round-Up to Savings" which Rounds-Up each Purchase and places the remainder into savings. Is there a way that you can help me with finding a solution to be able to Enter the Purchase amount then the information automatically Rounds Up and Displays that Rounded up Number into my Excel Spreadsheet then displays the information of that Item Date and Item Description in a separate page Called "Savings Account" with that information adding to the Savings Account Page and showing the New Balance rather than doing it manually every time I update my Excel with new Purchases Made. I have looked for Templates using Google for this feature and found none anywhere, and have tried Discord Discussion Help Groups for Excel. Nothing has really helped.

I was thinking that the columns should look something like this where each Item Line will Display information in this order

Date > Description of Purchase > Payment / Purchase (-) > Rounded Amount > Total after Rounding > Deposit + > Account Balance.

Which in return will send the > Item Date, Description of Purchase, and Rounded Difference automatically to Savings Account Page. 

Also having the option to send amount from Checking Account Page to Savings Account Page by enter a new Entry with Set Key Word "Transfer to Savings" Triggering the Result to add a new Line on Savings Account Page. Also, Vice Versa From Savings Account "Transfer to Checking Account" Keyword Triggering Result to add New Entry on Checking Account Page.

I would like to be able to still manually add to Savings Account Page, or select from Checking Account Page "Transfer to Savings" & vice versa from Savings to Checking as Well.

Can anyone help me with this complex (well complex to me) Excel Question?

[#2]

@DC_BarTroN 

That's not a single question, for what it's worth. It's a whole bunch of questions.

But that quibble aside, may I suggest you check out the Excel function ROUNDUP, read about it in Excel help....figure out what kind of "ROUNDUP" you are asking for--up to the nearest dollar, up to the nearest 10 dollars, the nearest 100? Doing it only to the nearest dollar seems so trivial as to not be worth it.

So check out that function. Play with it a bit. Create your own rounding up formula--it's a fairly easy one to do, and since you already appear to have a check register working, adding this feature should be a good learning experience.

As for the rest of that, once you've gotten ROUNDUP to do it's job as you want it to, come back and upload a sample of your spreadsheet as it exists at that point.

[#3]

Hello @mathetes,

I took some time and was able to get the Roundup formula to do some of the simple tasks I wanted the Roundup formula to complete. Although I couldn't figure out how to get the information to display on a separate page. So, I thought I'd try a simple Value Lookup, and use that as a constant update number on the Savings Account Page then just enter Transfers Manually, but that was unsuccessful. Here is what I am currently working on though.

I still would like to accomplish what I mentioned in the first reply if you see it possible.

Thanks,
DC_BarTroN

[#4]

@DC_BarTroN 

I'm a firm believer in single databases, wherever possible, using attributes in one or more columns to differentiate for reporting and summarizing.

So here, for instance, the truth is that you can see the rounding up and transferring to Savings of $xx.xx  is really part of the same, single transaction as that recorded in the Checking side of things. (That's evident even in the fact that in your two sheets, for those rounded-up transactions, the date and time stamps are identical.)

It's really one transaction, albeit and extended transaction, one with multiple parts affecting two accounts. But at bottom, one transaction. So it can easily be recorded on one line as both a Checking transaction AND a Savings transaction.

See the attached for how this resolution would work.

[#5]

@DC_BarTroN 

Kept thinking about it, and came up with a minor revision in part just to illustrate a simple way to change the ROUNDUP formula itself, should you decide to be more aggressive in the amounts you devote to Savings.

This uses a named range (set up on the second worksheet, which I've named "Bus Table" to indicate the fact that you can add other tables there. There's a named range created, called "RoundUpDeg" --to enable easy changes to the degree you do your rounding. Change the value in cell B1 to -1 and your rounding goes to the nearest 10 dollar figure, greatly increasing you savings rate, yet still (hopefully) not breaking your checking account balance. The formula need never be changed, just the value in that single cell.

=ROUNDUP(D5,RoundUpDeg)

[#6]

@DC_BarTroN 

A further revision, mostly cosmetic but with improved clarity and efficiency in mind. What I've done is put the date and time fields in the middle. Did this so that when you do a Savings Account transaction off to the right, it still is clear that the date and time have been used, even though it's blank on the left. I am assuming you make deposits to and withdrawals from the savings account independent of checking transactions.

I realize, of course, that I'm suggesting an altogether different way to accomplish what you were wanting to do. Turning what would be two "clearly distinct" sheets into a combined one is (some might say) a radical revision. It does seem to me though that unless your savings account is extremely active and needs a separate sheet, your main "presenting issue" of taking the rounded up delta and adding it to the Savings Account is really more neatly and clearly handled by combining the two records rather than by generating a separate  sheet.

Anyway, see what you think here. I've entered two 'test" Savings-only transactions.

By the way, you are to be commended for the level of sophistication shown in the formulas in your worksheet. You clearly know your way around Excel.