Spending plan and split transactions: a duplication issue

Luis Diego
Luis Diego Member

Hi. I just want to confirm if what I am trying to implement can be done in Simplifi at the moment, if it should be done in a different way, if it is a feature that does not exist yet or if I have encountered a bug.

There is a transaction that appears in my spending plan. When I correctly recategorize it as a split transaction with one part being an expense and the other part a transfer, the FULL amount appears subtracting twice: once as a bill and once as a transfer. Let me explain.

So I'm working with a manual checking bank account and a manual liability account that corresponds to a house mortgage. There is a monthly bill in the checking account that corresponds to the mortgage monthly payment. Each payment includes a part that corresponds to interest and a part that corresponds to amortization, but the bank statement shows the full amount as one transaction, so Simplifi has no way of knowing how much goes where. There is a separate section in the bank statement where I can review how much is interest and how much is amortization each month.

When I import that transaction using a .csv file it is categorized using rules, so that the full amount falls in the Interest category. Also the transaction is successfully recognized as a recurring bill (the recurring bill series was set up so that the full amount corresponds to the same Interest category, since I cannot create split recurring bills). So far so good. Since in reality, part of the transaction corresponds to amortization, after import and recognition I edit the transaction so that it is a split one (so each month a part of it goes to Interest and the other part becomes a transfer that reduces the mortgage). This works beautifully in reports.


The issue arises when I try to add this transaction to the Spending Plan. I want to track both parts, the outgoing interest as a bill, and the money out transfer as an included transfer. Why? Because both are obligations that must be met each month. So, I go to the checking account and allow these transactions to appear in reports and spending plans (so that the money out part is tracked). When I go to the Spending Plan the full amount is deducted twice: a full deduction as bill, the full deduction as a Transfer. This does not happen in Reports: the interest category only shows the right, reduced amount.

What is going on?

Comments

  • UrsulaA
    UrsulaA Superuser ✭✭✭✭
    edited January 27

    From what you describe, you need to select either the transfer or the outgoing full transaction in the spending plan. Recognizing both is creating your duplicate issue. The entire amount of the split transaction will be recognized in the spending plan.

    Transfers are excluded by default in the spending plan, so that is why the issue is not there. Can you exclude the transfer in your spending plan and see if the issue is resolved?

    Update: Am I understanding that you have 2 transactions - one the payment (split into interest and principal) and the other one being a transfer (from checking to mortgage)?

    "I want to track both parts, the outgoing interest as a bill, and the money out transfer as an included transfer."

    Simplifi User Since Nov 2023

    Minter 2014-2023

    Questionable Excel before 2014 to present

  • SRC54
    SRC54 Member ✭✭✭✭
    edited January 27

    @Luis Diego Yes, I have reported this bug. It should only count the part of the transaction that is a transfer instead of duplicating the entire thing as a transfer plus expense so it is effectively counted twice. The workaround is to go to Transfers in the spending plan and enter a custom transfer total. Mine is -674.08. This fixes it. I have the problem when I have a transfer from a paycheck. It won't hurt to contact Support and tell them this isn't how it's supposed to work, and it works correctly by the way on the mobile app (at least it did last time I checked). Of course, you could exclude the transaction altogether from the Spending Plan but I obviously have to count my paycheck! And I understand you need to count it too.

    Another fun thing is that if you do the transfer as an expense (I realize it isn't) and that expense isn't part of your planned spending, it counts it twice again in the Other Spending category! BUT if you have that category set up in Planned Spending, it works right and doesn't count it twice. (At least it didn't last week).

    FWIW, Support tells me that this is a bug (but they also say it is as designed, but it's not since it works right (or did) in the Mobile app. They say they are working on the Spending Plan but as of today and 3.97.0, it's still broken in the web app.

    I hope this helps some. I also assume you are talking about the web app. Check to see if it isn't right in the mobile app. And let us know what you find.

    Edit: I just checked the mobile app, and they've broken it there too, so it now works the same in both web app and mobile app. I guess that is progress as they can NOW say it is done that way by design. LOL. Some design. Simplifi does NOT really like split transactions. The transfer should be ignored whether it's split or not! And yes, they need a paycheck transaction AND they need splits in Recurring transactions too.

    Steve
    Quicken Simplifi Since 2021
    MS Money/Quicken Classic Since 1991

  • Luis Diego
    Luis Diego Member

    Hi Ursula. There is only one transaction. It is a bank deduction of, let's say, $1000, that is categorized using the split transaction function: $700 go to Interest and $300 become a Transfer to the Mortgage account (so that the debt decreases by $300). Yes, I would select one or the other in the spending plan if there was a way to do it with split transactions, but since I want to track the money out side, there is no way to tell Simplifi to track the expense and not the transfer. If I could, it would work but through a bug, because the spending plan should only recognize as a bill the portion of the split transaction that is effectively assigned to the Interest expense. I believe something like this can be done in Quicken. Quicken would even calculate each payment interest and amortization over time when you define an interest rate and number of payments.

  • Luis Diego
    Luis Diego Member

    @UrsulaA
    Thanks for your suggestion. It is a single transaction that on one end is a bank deduction (let's say $1000) and it distributed using the split transaction feature. So, about $700 is categorized as Interest (an expense category) and the rest ($300) is a transfer to the Mortgage Liability Account, reducing the debt by that amount. This is how it was done in Quicken. Quicken would even calculate the monthly interest and amortization automatically for recurring transactions when given an interest rate, starting amount for the Mortgage and number of payments. So to answer your suggestion, I would only include the expense if it were possible to choose only the expense part or the transfer par in a split transaction. In any case it shouldn't work because the full amount should not be categorized as a bill. It would give the right balance, but in a wrong way.

  • Luis Diego
    Luis Diego Member

    @SRC54 Hey Steve. Thanks for answering. I'll implement the workaround for now.

    I hadn't noticed the other "fun thing" you explain, I just checked other split transactions and it happens as you describe. I believe this is a big issue, because it can lead to significant errors when using the spending plan as a decision making tool. And it can be overlooked and escape detection unless we are manually adding things to check. Maybe split transactions shouldn't be allowed until this is fixed because there is no warning this can happen. And, as you pointed out, it can be an issue with some of the most basic and relevant transactions we deal with: a net paycheck that already has automatic deductions of insurances, taxes and retirement investments, or liabilities such as mortgages, which tend to be important.

    I'll contact support so that this is pointed out by more people. Thanks.

    @Coach Natalie


  • UrsulaA
    UrsulaA Superuser ✭✭✭✭
    edited January 28

    @SRC54 - thanks for posting, the bug persists. I saw on Reddit a post by QuickenLee, one of the moderators for the Simplifi Money sub. He states that split transactions are being re-designed. Hope the enhancement comes soon and alleviates what you and @Luis Diego are experiencing.

    There’s a lot to look forward to in 2024, too. Here’s what’s coming up (relevant items to this discussion in bold):

    • CSV import improvements (like importing tags and memos from Mint) - this one was done earlier this month, the net worth import from other financial apps, including Mint is in progress per the Community post.
    • Enhancements to goals and spending plan features 
    • A redesign of transaction splits
    • Credit score integration
    • Improved rules to give you more control over Simplifi’s automation

    https://www.reddit.com/r/simplifimoney/s/ABYBRMxohz

    Simplifi User Since Nov 2023

    Minter 2014-2023

    Questionable Excel before 2014 to present

  • Luis Diego
    Luis Diego Member

    Hi @Coach Natalie, the full amount is shown twice and added twice in the spending plan. It is not just that the total split amount is shown, it is added in full to the Bills total, and added in full again to the tracked transfers section, effectively reducing the income by twice the amount in the spending plan.
    So, if the split transaction full amount is $120, where $100 are categorized as Interest (an expense category) and $20 are categorized as a transfer to the mortgage liability account, when I open the Spending Plan it is deducted in both sections. I calculated the totals manually and it checks out.

    I'll study the posts you suggest and check back with you. Thanks for your help.

  • Luis Diego
    Luis Diego Member

    Regarding the posts you suggested:
    1. Amounts displayed on transaction lists: yes, I've seen this, but it is different. When I study the bubbles that appear in the "Other spending" section and there are some split transactions, the full amount is shown, but only the part assigned to the category is added. It's confusing but I see no sum errors.
    2. Ability to ignore part of a split transaction: yes I believe this is part of the issue and a nice to have. When you have a mixed expense & transfer split, sometimes you want to ignore the transfer in the spending plan, but consider the expense. It would be useful in that case. In my case it would become a workaround that shouldn't work, haha. If I could ignore the transfer part (which I don't want to ignore) the total would be considered once (in the bills section). That would be good for the totals, but it is still wrong, since it would still be considering the total amount as a bill. I believe the current issue is that in the view I show in the picture, splits are not added correctly for the Bills, Suscriptions and Transfer sections. As you can see, I had to set up a Custom transfers total, because the split bill/expense in question was being deducted twice.

    3. Link Part of a Split Transaction to a Recurring Bill: Yes please! And not only this, allow split recurring bills. And to nail it all, let us create a rule that recognizes a Payee and then categorizes the transaction using a split rule (like, x amount or x percentage goes to this category and the rest to that other one). In Quicken, when I created a Liability account it would let me create a series of recurring bills that would show the same total monthly payment, but would already have a split defined using amortization rules. That was neat!
    4. #TipTuesday: Using splits in the Spending Plan: great article BTW! This almost gets to the issue I found. All of that works as it is stated, and I think everything works ok until a part of the split goes to Transfers. That's where something is broken for me. Could you try and reproduce the error I'm seeing?
    a. Create a recurring monthly bill for $1200 that is categorized as any trackable expense (let's say Interest)
    b. Manually enter this month's transaction for $1200, categorized as that same expense.
    c. If they are not linked automatically, link them. The transaction should appear as paid in the spending plan for this month.
    d. Now go to the transaction and split it. Half will still be Interest, and the other half will be a transfer to a specific account. Make sure that this transaction is not ignored in Spending Plans or Reports.
    e. Now, go back to this month's spending plan and manually check the totals for Bills and for Transactions. If you get the same issue that I get, the bills total will include the full transaction total and the Transfers total will include it again. Your "Income after Bills and Savings" will show an error of -$1200 and it will show also at the "Left at the End of Month" This is specific to this section, I don't see the problem in reports, where splits are managed accurately, or in the "Other Spending Section." Although, according to @SRC54 the issue persists with split transactions that have one part in the Planned Spedning and another part landing in the Other spending sections.
    I'll create a dummy account to show this.


  • Luis Diego
    Luis Diego Member

    @SRC54 @Coach Natalie
    I created a dummy account with just one transaction to show you the issue. Where should I post this so that someone looks at it as bug?

    Here are the screenshots that show what happened as went over the steps described:

    1. Create a new Simplifi account and add two accounts, a Bank account with $10,000 and a Mortgage account for only $5000.

    2. Create a recurring monthly bill for $1200 that is categorized as Loan Interest


    3. Manually enter this month's transaction for $1200, categorized as that same expense.


    4. They are not linked automatically, so link them. The transaction should appear as paid in the spending plan for this month.

    So, the spending plan for this month only shows a bill for $1200. Since there is no income, this was a really bad month.

    5. Now go back to the transaction and split it. Half will still be Interest, and the other half will be a transfer to the Mortgage account. This is how Liabilities should be handled when treated as accounts.

    So, as you can see here, the Mortgage liability is reduced by the amortization amount ($600) and the bank account by the total transaction amount. The rest is Interest expense.

    6. So far so good. Noe, make sure that this transaction is not ignored in Spending Plans or Reports. Why? Because I want to be sure that I consider the total transfer for the payment each month, even though a part of it is, deep down, just a reduction in an amount owed. Simplifi by default marks the full transaction as ignored the moment I add a Transfer, which in itself can be problematic if you want to track at least the the expense part. So uncheck the exclusion.


    7. Now, go back to this month's spending plan and manually check the totals for Bills and for Transactions. Look at the values to the left and to the right. The only one correct is the one down to the right.

    So, in the bills section, it should be-$600 and in the transfers section, it should be -$600. So that the total below to the right is -$1200 (the only one that checks out). In the left pane you can see the amount is duplicated (-$2400) all the way to the available amount.
    So, this is a bug, and one that may go unnoticed.

  • RobWilk
    RobWilk Superuser ✭✭✭✭✭

    While it takes a long time to get to the end (to see the problem), there is a visible issue there… Not selecting only the relevant split portion and taking the total transaction amount under two different categories effectively doubles the amount 'spent.' This 'should' be easy for them to understand and might (in my non-employee opinion) deserve higher priority than some of the new features people (myself included) have been asking for because this comes down to incorrect calculations, which are 'dangerously bad' for a financial app, but definitely fix-able.


    Rob Wilkens

  • UrsulaA
    UrsulaA Superuser ✭✭✭✭

    @Luis Diego @SRC54 @RobWilk - You are all correct. This bug needs fixing and priority/escalation. The spending plan is a crucial tool within Simplifi and needs accuracy.

    Simplifi User Since Nov 2023

    Minter 2014-2023

    Questionable Excel before 2014 to present

  • SRC54
    SRC54 Member ✭✭✭✭

    @Luis Diego Thanks for keeping on with this. In my Spending Plan, if I count the transfer portion as an expense that I don't keep up with in Planned Spending, it counts the whole transaction minus the transfer in Other Spending. In other words if the gross salary is $5K and the transfer is $500, it will add +4500 to other Spending increasing my leftover cash by that amount.

    If I do it as a transfer, it counts the net amount of my paycheck in Transfers as say +4000 (because of taxes).

    The Spending Plan has always put the bottom line in the Bills Section and ignored splits, so I don't think that is different for me anyhow, and it does correctly include my splits in my Planned Spending categories (other than the transfer), which will be the same as my paycheck if I don't enter a Custom Transfer Total. The workaround for Other Spending is to include it in a Planned Spending budget.

    Steve
    Quicken Simplifi Since 2021
    MS Money/Quicken Classic Since 1991

  • Luis Diego
    Luis Diego Member

    There's an issue with split transactions in the Spending Plan that given certain user actions can create serious errors in the reported distribution of expenses and transfers, as well as the totals for the month.

    Look at these values? Do you see something wrong? There is only one (split) transaction for $1200.

    I created a dummy account with just one transaction to show the issue. Here are the steps I followed:

    1. Create a new Simplifi account and add two accounts, a Bank account with $10,000 and a Mortgage account for only $5000.

    2. Create a recurring monthly bill for $1200 that is categorized as Loan Interest


    3. Manually enter this month's transaction for $1200, categorized as that same expense.


    4. They are not linked automatically, so link them. The transaction should appear as paid in the spending plan for this month.

    So, the spending plan for this month only shows a bill for $1200. Since there is no income, this was a really bad month.

    5. Now go back to the transaction and split it. Half will still be Interest, and the other half will be a transfer to the Mortgage account. This is how Liabilities should be handled when treated as accounts. 

    So, as you can see here, the Mortgage liability is reduced by the amortization amount ($600) and the bank account by the total transaction amount. The rest is Interest expense.

    6. So far so good. Now, make sure that this transaction is not ignored in Spending Plans or Reports. Why? Because I want to be sure that I consider the total transfer for the payment each month, even though a part of it is, deep down, just a reduction in an amount owed. Simplifi by default marks the full transaction as ignored the moment I add a Transfer, which in itself can be problematic if you want to track at least the the expense part. So uncheck the exclusion.


    7. Now, go back to this month's spending plan and manually check the totals for Bills and for Transactions. Look at the values to the left and to the right. The only one correct is the one down to the right.

    So, in the bills section, it should be-$600 and in the transfers section, it should be -$600. So that the total below to the right is -$1200 (the only one that checks out). In the left pane you can see the amount is duplicated (-$2400) all the way to the available amount.
    So, this is a bug, and one that may go unnoticed.

  • Luis Diego
    Luis Diego Member

    Look closely at this Spending Plan report. There is only one transaction (a split one) for $1200. But it shows twice and it is substracted twice in almost every section of the report. This causes an error in the distribution of expenses and in the total shown.


    I created a dummy Simplifi account with just one transaction to show the issue. These are the steps I took:

    1. Create a new Simplifi account and add two bank accounts, a Checking Bank account with a $10,000 balance and a Mortgage account with a $5000 debt.

    2. Create a recurring monthly bill for $1200 that is categorized as Loan Interest


    3. Manually enter this month's transaction for $1200, categorized as that same expense.


    4. They are not linked automatically, so link them. The transaction should appear as paid in the spending plan for this month.

    So, the spending plan for this month only shows a bill for $1200. Since there is no income, this was a really bad month, but no errors (yet)

    5. Now go back to the transaction and split it. Half will still be Loan Interest, and the other half will be a transfer to the Mortgage account (Transfers>Mortgage). This is how Liabilities should be handled when treated as accounts. 

    So, as you can see here, the Mortgage liability is reduced by the amortization amount ($600) and the bank account by the total transaction amount. The rest ($600) is Interest expenses so you can't see it in a balance, of course.

    6. So far so good. Now, make sure that this transaction is not ignored in Spending Plans or Reports. Why? Because I want to be sure that I consider the total transfer for the payment each month. By default, Simplifi marks the full transaction as ignored the moment I add a Transfer, which in itself can be problematic if you want to track at least the the expense part. So, in this case, uncheck the exclusion.


    7. Now, go back to this month's spending plan and manually check the totals for Bills and for Transactions. Look at the values to the left and to the right. The only one that is correct is the one down to the right.

    So, in the bills section, it should be-$600 and in the transfers section, it should be -$600. So that the total below to the right is -$1,200 (the only one that checks out). In the left pane you can see the amount is duplicated (-$2,400) all the way to the available amount.
    So, this is a bug, and one that may go unnoticed anytime a split is added to a transaction that is marked as income/bill/subscription and one part of the split is a transfer you also want to track. And in any case, the totals for each sub-section are wrong, because the split subtotals are ignored.

  • Came to report this. Glad to know it's already being worked on. Thank you!

  • Coach Kristina
    Coach Kristina Moderator admin

    Hello @Luis Diego,

    To look into this further, is the mortgage account in the steps to replicate the issue an asset account or a liability account? Why are you wanting only 1/2 of the payment to reflect in the mortgage account?

    From what I can see, the root cause of the issue you're seeing is that, for the purposes of the Spending Plan, Quicken Simplifi treats the entire transaction as a transfer. It correctly sees the bill reminder for 1200 that you created as 1200. When you force it to not exclude transfers, it also sees the transaction that you linked to the bill reminder, essentially causing it to see the 1200 twice.

    From the process you describe, it sounds like what you want a way to create a bill reminder with a split transaction so that you can reflect the principal and interest paid on the mortgage loan. Is that correct?

    I look forward to your response!

    -Coach Kristina

  • Luis Diego
    Luis Diego Member

    Hi @Coach Kristina .

    The credit (mortgage) account is set as a liability. Because you owe the money to the bank. There's also an asset, of course, the house in itself, that now is yours. I didn't include the asset in the example, because it is not necessary to explain the issue. The asset would only change if sold, if appreciated over time or because of depreciation. Both the asset and the liability should exist until the credit is fully paid. The liability decreases each month. So, as you make each monthly payment, your net worth increases. You own more of the house, so to speak.

    Strictly speaking, every time you are making a liability monthly payment, a portion of that payment is an expense (the interest, or the cost of money you owe over that month) and the rest is the actual payback of the money you initially received (called amortization). Amortization is not an expense and is not subject to taxes in general. It is literally a transfer, a payment of money owed. Regarding the 1/2, It's not half and half in reality, that's just something I did to illustrate the issue. Unless you sign up for custom types of credits (such as the ballon payment kind), usually, each month's interest is aprox. the monthly interest rate times the amount you still owe. The rest is amortization. You can't apply the full monthly payment to the mortgage because that would imply there is no interest (Unless you use the future value of the money to create the liability but that would yield an inaccurate current net worth).

    So, to give you an example, let's say I get a loan of $100,000 at an annual interest rate of 5% that I should pay in 120 months. Using Excel's PMT function, I get a monthly payment of $1,060.66.
    So, the first month I owe $100,000. After one month the interest I owe is $100,000x(0.05/12)=$416.67. So when I make my first payment of $1,060.66, the bank will take $416.67 and apply it as interest paid, and the rest will reduce the amount owed (1060.66-416.67=643.99). So 100,000-643.99=$99,356.01 → that's how much I owe after the first payment. Next month the interest will be $99,356.01x(0.05/12)=$413.98, and the amortization 1060.66-413.98=646.67, and I will owe 99,356.01-646.67=$98,709.34, and so on until it is paid after 120 months. (See the attached spreadsheet with the full amortization table). This is what most banks do under the hood. Sometimes other fees are added, and of course, there are also insurances, but let's keep it simple.


    The root cause I see is that Simplifi treats the full transaction as both a bill and as a transfer. In coding terms it is as if there are two separate For-Next loops, one for bills, another one for transfers that both look for transactions that meet the criteria to be added, and since this transaction is seen as both a bill and a transfer, adds up in both sections. This will be solved, I think, if in the case of Split Transactions, Simplifi examined the subtotals, adding only the part that applies.

    Yes, what you asked at the end is correct.

  • Coach Kristina
    Coach Kristina Moderator admin

    Thank you for your reply,

    For the loan tracking issue, it sounds like you're wanting functionality that Quicken Simplifi does not currently have. There are some existing Idea posts asking for similar functionality though. Please review these and add your vote if it's something you'd like to see implemented into Simplifi in the future:

    If none of these quite match what you're looking for, then I would recommend creating a new Idea post. You can use this link for instructions on how to do that.

    For the issue with the Spending Plan treating the entire split transaction as a transfer (when only part of the transaction is a transfer) and counting the entire transaction twice as a result (once with the bill reminder and again as a transfer), that does sound like a bug. Since I'm not able to process escalations, I would recommend reaching out to support directly for faster service. You can use this link for directions on how to contact support.

    I hope this helps!

    -Coach Kristina

This discussion has been closed.