dcalabre
asked on
Quicken 2000
Setup my car loan (liability) with the normal "Create Loan" in quicken.
Setup my car as an asset (Q2000 calls the account a "Vehicle")and these two accounts a sort of linked together.
In the loan setup you are asked what category to apply the interest but you are never asked where to apply the rest of the payment. After a payment is made, the split transaction automatically shows the account that I paid from (in this case my checking) which is the normal way Q2000 applies money from one account to another.
I would like to apply it to the account "Auto", otherwise I do not get a true accounting of what it costs to own my car - Payment/fuel/maint./etc.
If I change the split transaction that the loan puts in my checking account I will alter the balance of the account and the balance will be off on the loan. Any ideas??
Setup my car as an asset (Q2000 calls the account a "Vehicle")and these two accounts a sort of linked together.
In the loan setup you are asked what category to apply the interest but you are never asked where to apply the rest of the payment. After a payment is made, the split transaction automatically shows the account that I paid from (in this case my checking) which is the normal way Q2000 applies money from one account to another.
I would like to apply it to the account "Auto", otherwise I do not get a true accounting of what it costs to own my car - Payment/fuel/maint./etc.
If I change the split transaction that the loan puts in my checking account I will alter the balance of the account and the balance will be off on the loan. Any ideas??
Patricia Siu-Lai Ho
dcalabre, <where to apply the rest of the payment> what are they, please advise your details. pslh
The way my Quicken 2000 setup works in such a case is that the asset account is creditted with the non-interest and/or non-insurance payments, which is the principal piece (that which is the actual dollar amount against the actual value of the product, as you probably know). Perhaps I'm misunderstanding the issue. If I don't like the pre-existing categories, I create my own and decide which transfer account to setup if the existing structure isn't what I want. It's a great program with great flexibility.
Asta
Asta
ASKER
I'm sorry if I am not explaining this well ...
The categories I have setup are fine.
Major cat -Auto
Sub cat -loan payment
Sub cat -Maintenance
Sub cat -Wash/Wax
Sub cat -Fuel
Sub cat -License and fees
Sub cat -Insurance
If I do this report Topic - "Where am I spending my money" Select a report or graph - "Buget Report" it will list all money I have spent on Auto, all the subs are broken out then it sub totals the entire catigory "Auto" for a total Auto Expense except loan payment. The loan payments (which are the majority of the money being spent on my car)are all under the category [Don's Ford credit Loan] at the bottom of the category and transfer list because it is a separate account (which it has to be because it's a loan) do you see how it's a circle. The loan must be a separate account because it's a Liability Account (bottom portion of the list where all the other acconts are), yet I want the money spent to appear under the category "Auto".
The categories I have setup are fine.
Major cat -Auto
Sub cat -loan payment
Sub cat -Maintenance
Sub cat -Wash/Wax
Sub cat -Fuel
Sub cat -License and fees
Sub cat -Insurance
If I do this report Topic - "Where am I spending my money" Select a report or graph - "Buget Report" it will list all money I have spent on Auto, all the subs are broken out then it sub totals the entire catigory "Auto" for a total Auto Expense except loan payment. The loan payments (which are the majority of the money being spent on my car)are all under the category [Don's Ford credit Loan] at the bottom of the category and transfer list because it is a separate account (which it has to be because it's a loan) do you see how it's a circle. The loan must be a separate account because it's a Liability Account (bottom portion of the list where all the other acconts are), yet I want the money spent to appear under the category "Auto".
You explained it all perfectly, thank you. It sounds as though you're set up as I am; and that you should be getting accurate analytical results as setup. I constantly modify (customize) my reports to do whatever analyses I need from any given set of my accounts, including the asset and liability accounts via the customize options once a canned report has been produced. I then add whatever key is needed, and make sure that I use the fields that apply to my analysis that brings together the items with a common category by modifying the "includes statements under accounts, categories, descriptions" or whatever ties them together.
If, for example, you were to pay $100 from any given account you have established for AUTO, and this could be the parent directory or heading as you've mentioned in this scenario, it may be paid from a checking, savings, Visa, etc. to the Loan Company, the as a rule this is a calculation that then distributes the changing amounts monthly to your actual asset account where the balance decreases when the principal is applied and the other calculated increment is the interest value. So the actual expenses that you're putting out for your auto, should be accurately reflected as you now have it set up, since the total payment of, say, $100 in this example from your checking account would be the loan PLUS the interest which through the split transaction would then be sent to the loan account. Using the report customization tools can help create any vision and inclusions you wish. The actual Loan account that you have set up will show the actual (or should) portion of the total monthly payment that was applied to the principal only portion. Thus if this were included also in your accounting report it would create incorrect values.
My input here is from a standard mortgage setup versus auto loan, so perhaps there's some difference, but don't think so.
If, for example, you were to pay $100 from any given account you have established for AUTO, and this could be the parent directory or heading as you've mentioned in this scenario, it may be paid from a checking, savings, Visa, etc. to the Loan Company, the as a rule this is a calculation that then distributes the changing amounts monthly to your actual asset account where the balance decreases when the principal is applied and the other calculated increment is the interest value. So the actual expenses that you're putting out for your auto, should be accurately reflected as you now have it set up, since the total payment of, say, $100 in this example from your checking account would be the loan PLUS the interest which through the split transaction would then be sent to the loan account. Using the report customization tools can help create any vision and inclusions you wish. The actual Loan account that you have set up will show the actual (or should) portion of the total monthly payment that was applied to the principal only portion. Thus if this were included also in your accounting report it would create incorrect values.
My input here is from a standard mortgage setup versus auto loan, so perhaps there's some difference, but don't think so.
dcalabre, Set up a scenario for you like this (accounting theory- double entry method): -
(I) Create accounts
=============
Create/inspect account in asset and liability: -
1. Account [ A-vehicle-xxx] Type: [Asset] Description [car-xxx]
2. Account [ L-vehicle-xxx] Type: [Liability] Description [car-xxx]
Create categories as :
1. Major cat -Auto
Sub cat -loan payment
Sub cat -Maintenance
Sub cat -Wash/Wax
Sub cat -Fuel
Sub cat -License and fees
Sub cat -Insurance
Sub cat- Interest payment
2. Major cat -car purchase original value [Discretionary Expense, non tax]
(II) Create a new Loan
===============
New loan>select existing account [L-vehicle-xxx ] ,
where [L] stands for liability in the list of account.
[L-vehicle-xxx] for first vehicle car number "xxx", [L-vehicle-yyy ] for second car number "yyy" etc..
Enter the original amount that was borrowed. Original Balance: [100,000.00]
Enter the original length of the loan. Original Length: 5 years (60 payments)
Enter the payment period for this loan. Standard Period: monthly (60 payments)
Enter the compounding period for this loan. Compounding Period: monthly (60 payments)
Then follow to fill up the interest rate and principal (quicken calculated P+I=2,124.70 per month) etc.
In the dialogue Set Up Loan Payment, Click [Edit payment] button.
Current Interest Rate: [10%]
Principal and Interest [2,124.70 ]
Other amount in payment: 0.00 [Edit]
------------------
Full Payment: 2,124.70
Click the [Edit] button next to the Other amount in payment and enter transaction like this e.g. : -
1. [Auto:Insurance ] Memo: [ car-xxx-insurance ] Amount: [100.00]
2. [Auto:loan payment ] Memo: [ car-xxx Original value ] Amount: [ 0.00]
3. [Car purchase original value ] Memo: [ car-xxx Original value ] Amount: [ 0.00]
Split Total: 100.00
Remainder: 0.00
Transaction Total: 100.00 click [Adjust] button to update the value.
OK. Now it looks like this: -
Current Interest Rate: [10%]
Principal and Interest [2,124.70 ]
Other amount in payment: 100.00 [Edit]
------------------
Full Payment: 2,224.70
Type: [Payment] [Payment method] - set this either memorized or scheduled
Payee: [Don's Ford credit Loan]
Memo: [car-xxx mortgage]
Category for interest: [Auto: Interest payment ]
remark: drag to change from [Major cat- Interest payment] to [Subcat:Auto-Interest payment]
OK.
(III) Checking transactions for asset and liability for the new Loan
==========================
You either associate the liability [ L-vehicle-xxx] with the new asset [ A-vehicle-xxx], if A-vehicle-xxx not exist. Follow the wizard and answer YES to associate and enter value=$100,000.00
Or, If asset [ A-vehicle-xxx], you can go to the register liability and select the original transaction but edit it by "create a transfer",
Transfer from account [ L-vehicle-xxx] to account [ A-vehicle-xxx] value $100,000.00
(IV) Make Payment for the loan
====================
Now in the register, click [ L-vehicle-xxx], you will fine a transaction increase $100,000.00
click [A-vehicle-xxx], you will fine a transaction increase $100,000.00
When make payment, click the Loan, [Make Payment], select regular.
Click the [split], you will find: -
Category
1. [L-vehicle-xxx] Amount: [1,291.37]
2. [Auto: Interest payment] Amount: [ 833.33]
3. [Auto:Insurance] Amount: [ 100.00]
4. [Auto:loan payment ] Amount: [0.00] , change amount value by enter value [1,291.37]
5. [Car purchase original value ] Amount: [0.00] , change amount value by enter value [-1,291.37]
Split Total: 2,224.70
Remainder: 0.00
Transaction Total: 2,224.70 click [Adjust] button to update the value.
Click OK.
Back to the Make Regular Payment window: enter Number [next check number 123456],
Click OK to confirm transaction.
Now click register, you will find followings transactions
>> Checking: Num [123456] [Don's Ford credit Loan ] Payment: [2,224.70] Balance [-2,224.70]
When click [-Split-], you will find the transactions as listed as above breakdown 5 items as followings.
1. [L-vehicle-xxx] Amount: [1,291.37]
2. [Auto: Interest payment] Amount: [ 833.33]
3. [Auto:Insurance] Amount: [ 100.00]
4. [Auto:loan payment ] Amount: [1,291.37]
5. [Car purchase original value ] Amount: [-1,291.37]
>> L-vehicle-xxx liability:
[Don's Ford credit Loan ] Decrease: [1,291.37] Balance [1,291.37] Carry forward balance [98,708.63]
>> A-vehicle-xxx asset: Carry forward balance 100,000.00]
(V) Inspect the Net Worth and Expense report
==========================
>> Click to view the Net worth report: -
ASSETS Balance
Cash and Bank Accounts
Checking -2,224.70
Other Assets
A-vehicle-xxx 100,000.00
--------------
Total Assets 97,775.30
LIABILITIES
Other Liabilities
L-vehicle-xxx 98,708.63
--------------
Total Liabilities 98,708.63
--------------
Overall total -933.33
========
>> Click to view the Report on Income and Expense - Summary
EXPENSES
Auto:
Insurance 100.00
Interest payment 833.33
loan payment 1,291.37
--------------
TOTAL Auto 2,224.70
Car purchase original value -1,291.37
-------------
TOTAL EXPENSES 933.33
-----SUMMARY OF THE SCENARIO---
In this way, you will not offset the account by additional compensated accounts, in brief, in trial balance.
Debit Credit
Expenses : Car purchase original value $principal
Expenses : Auto: loan payment $principal
Remark calculated substituted in Above:
loan amount= $100,000.00
principal for each payment (first here): $ 1,291.37
Interest for each payment (first here): $ 833.33
Lump sum for each payment for the loan: $ 2,124.70
Additional payment for Don's Ford credit Loan for each payment (if any): $100.00
Total expense for each payment for the loan: $ 2,224.70
------------
Let me know if you've any queries and let me know if it worked out. pslh
(I) Create accounts
=============
Create/inspect account in asset and liability: -
1. Account [ A-vehicle-xxx] Type: [Asset] Description [car-xxx]
2. Account [ L-vehicle-xxx] Type: [Liability] Description [car-xxx]
Create categories as :
1. Major cat -Auto
Sub cat -loan payment
Sub cat -Maintenance
Sub cat -Wash/Wax
Sub cat -Fuel
Sub cat -License and fees
Sub cat -Insurance
Sub cat- Interest payment
2. Major cat -car purchase original value [Discretionary Expense, non tax]
(II) Create a new Loan
===============
New loan>select existing account [L-vehicle-xxx ] ,
where [L] stands for liability in the list of account.
[L-vehicle-xxx] for first vehicle car number "xxx", [L-vehicle-yyy ] for second car number "yyy" etc..
Enter the original amount that was borrowed. Original Balance: [100,000.00]
Enter the original length of the loan. Original Length: 5 years (60 payments)
Enter the payment period for this loan. Standard Period: monthly (60 payments)
Enter the compounding period for this loan. Compounding Period: monthly (60 payments)
Then follow to fill up the interest rate and principal (quicken calculated P+I=2,124.70 per month) etc.
In the dialogue Set Up Loan Payment, Click [Edit payment] button.
Current Interest Rate: [10%]
Principal and Interest [2,124.70 ]
Other amount in payment: 0.00 [Edit]
------------------
Full Payment: 2,124.70
Click the [Edit] button next to the Other amount in payment and enter transaction like this e.g. : -
1. [Auto:Insurance ] Memo: [ car-xxx-insurance ] Amount: [100.00]
2. [Auto:loan payment ] Memo: [ car-xxx Original value ] Amount: [ 0.00]
3. [Car purchase original value ] Memo: [ car-xxx Original value ] Amount: [ 0.00]
Split Total: 100.00
Remainder: 0.00
Transaction Total: 100.00 click [Adjust] button to update the value.
OK. Now it looks like this: -
Current Interest Rate: [10%]
Principal and Interest [2,124.70 ]
Other amount in payment: 100.00 [Edit]
------------------
Full Payment: 2,224.70
Type: [Payment] [Payment method] - set this either memorized or scheduled
Payee: [Don's Ford credit Loan]
Memo: [car-xxx mortgage]
Category for interest: [Auto: Interest payment ]
remark: drag to change from [Major cat- Interest payment] to [Subcat:Auto-Interest payment]
OK.
(III) Checking transactions for asset and liability for the new Loan
==========================
You either associate the liability [ L-vehicle-xxx] with the new asset [ A-vehicle-xxx], if A-vehicle-xxx not exist. Follow the wizard and answer YES to associate and enter value=$100,000.00
Or, If asset [ A-vehicle-xxx], you can go to the register liability and select the original transaction but edit it by "create a transfer",
Transfer from account [ L-vehicle-xxx] to account [ A-vehicle-xxx] value $100,000.00
(IV) Make Payment for the loan
====================
Now in the register, click [ L-vehicle-xxx], you will fine a transaction increase $100,000.00
click [A-vehicle-xxx], you will fine a transaction increase $100,000.00
When make payment, click the Loan, [Make Payment], select regular.
Click the [split], you will find: -
Category
1. [L-vehicle-xxx] Amount: [1,291.37]
2. [Auto: Interest payment] Amount: [ 833.33]
3. [Auto:Insurance] Amount: [ 100.00]
4. [Auto:loan payment ] Amount: [0.00] , change amount value by enter value [1,291.37]
5. [Car purchase original value ] Amount: [0.00] , change amount value by enter value [-1,291.37]
Split Total: 2,224.70
Remainder: 0.00
Transaction Total: 2,224.70 click [Adjust] button to update the value.
Click OK.
Back to the Make Regular Payment window: enter Number [next check number 123456],
Click OK to confirm transaction.
Now click register, you will find followings transactions
>> Checking: Num [123456] [Don's Ford credit Loan ] Payment: [2,224.70] Balance [-2,224.70]
When click [-Split-], you will find the transactions as listed as above breakdown 5 items as followings.
1. [L-vehicle-xxx] Amount: [1,291.37]
2. [Auto: Interest payment] Amount: [ 833.33]
3. [Auto:Insurance] Amount: [ 100.00]
4. [Auto:loan payment ] Amount: [1,291.37]
5. [Car purchase original value ] Amount: [-1,291.37]
>> L-vehicle-xxx liability:
[Don's Ford credit Loan ] Decrease: [1,291.37] Balance [1,291.37] Carry forward balance [98,708.63]
>> A-vehicle-xxx asset: Carry forward balance 100,000.00]
(V) Inspect the Net Worth and Expense report
==========================
>> Click to view the Net worth report: -
ASSETS Balance
Cash and Bank Accounts
Checking -2,224.70
Other Assets
A-vehicle-xxx 100,000.00
--------------
Total Assets 97,775.30
LIABILITIES
Other Liabilities
L-vehicle-xxx 98,708.63
--------------
Total Liabilities 98,708.63
--------------
Overall total -933.33
========
>> Click to view the Report on Income and Expense - Summary
EXPENSES
Auto:
Insurance 100.00
Interest payment 833.33
loan payment 1,291.37
--------------
TOTAL Auto 2,224.70
Car purchase original value -1,291.37
-------------
TOTAL EXPENSES 933.33
-----SUMMARY OF THE SCENARIO---
In this way, you will not offset the account by additional compensated accounts, in brief, in trial balance.
Debit Credit
Expenses : Car purchase original value $principal
Expenses : Auto: loan payment $principal
Remark calculated substituted in Above:
loan amount= $100,000.00
principal for each payment (first here): $ 1,291.37
Interest for each payment (first here): $ 833.33
Lump sum for each payment for the loan: $ 2,124.70
Additional payment for Don's Ford credit Loan for each payment (if any): $100.00
Total expense for each payment for the loan: $ 2,224.70
------------
Let me know if you've any queries and let me know if it worked out. pslh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting.
ASKER
Not sure yet - It will take some time to process and test but thanks - it must work or you wouldn't have lost your mind on the keyboard like that.
dcalabre, Thanks.
Your requirement is not uncommon as I have dealt since 1988 using lotus 123 as accounting program.
There are two modules of accounting theory-single entry and double entry.
Most of the personal users would like the single entry method (S) -
money In - money out = balance of cash flow = (liability + income) - (expense) = asset (worth)
The business users would like the double-entry method (D)-
worth = asset - liability + capital
liabiity = AR (income) - AP (expense) + debt and loan + mortgage etc...
The design of quicken is originally a single entry accounting program. However, later they modified and imparted the double entry method so that more commercial people would be used.
In the quicken 2000, entries look like single entry but calculating module at the background is double-entry method.
That's why you see what you saw that the principal of loan or mortgage will be automatically split into one part (interest) to expense account and one part (principal) to asset account.
Therefore, just like adding appreciation/depreciation calculating method and foreign exchange calculating method, we add compensate account in either one account group by both credit (source) and debit (application) to balance the accounting module and not to offset it.
What I'm suggested to you is an approven design aiming for proprietor or small business owners. They will be reported in their comprehesive way (S) and not deviated from the report of those public accountant (D).
While you test, just add new loan and new expense account category. Then enter the sample data. Once you've found the report as my scenario, it is proved to work. Then reproduce the procedure of your current In/out for your mortgage of car (for next payment, it may be a true transaction or a test transaction). Later, delete all the testing/sample accounts/categories/loan and testing transactions after testing of the calculating scenario.
pslh
Your requirement is not uncommon as I have dealt since 1988 using lotus 123 as accounting program.
There are two modules of accounting theory-single entry and double entry.
Most of the personal users would like the single entry method (S) -
money In - money out = balance of cash flow = (liability + income) - (expense) = asset (worth)
The business users would like the double-entry method (D)-
worth = asset - liability + capital
liabiity = AR (income) - AP (expense) + debt and loan + mortgage etc...
The design of quicken is originally a single entry accounting program. However, later they modified and imparted the double entry method so that more commercial people would be used.
In the quicken 2000, entries look like single entry but calculating module at the background is double-entry method.
That's why you see what you saw that the principal of loan or mortgage will be automatically split into one part (interest) to expense account and one part (principal) to asset account.
Therefore, just like adding appreciation/depreciation calculating method and foreign exchange calculating method, we add compensate account in either one account group by both credit (source) and debit (application) to balance the accounting module and not to offset it.
What I'm suggested to you is an approven design aiming for proprietor or small business owners. They will be reported in their comprehesive way (S) and not deviated from the report of those public accountant (D).
While you test, just add new loan and new expense account category. Then enter the sample data. Once you've found the report as my scenario, it is proved to work. Then reproduce the procedure of your current In/out for your mortgage of car (for next payment, it may be a true transaction or a test transaction). Later, delete all the testing/sample accounts/categories/loan and testing transactions after testing of the calculating scenario.
pslh
I am happy you're pleased with the solution.
Asta
Asta