Example 25 - Tracking Outstanding Loan Balance

This example applies to our online demo Time Value of Money Calculator. The C-Value! program for Windows works in a similar way and has a few more features. Note, our online demo TVM calculator is limited to calculations using interest rates between 4.0% and 5.99%

Note:This topic has not been proofed and images need to be added. However, we believe that the steps are complete and accurate.

Managing a loan payment-by-payment. Tracking the outstanding balance.

1. Click the [New] button to clear any previous entries.
2. Set "Rounding" to "Ignore" by either:

• clicking on the "Rounding" button on the toolbar;
• clicking on the {Compute} menu choice and select {Rounding...};

1. One of the most frequently asked questions we hear is "I lent money to (my kid, a family member, a friend or a complete stranger). Now they pay me back when they feel like it. Can C-Value! record the payments, calculate what is applied to principal and interest and track the balance? Will it print a schedule to give to the borrower?"
2. Yes! and Yes!
3. The below are of course example settings. You'll need to adjust the setting according to your individual circumstances. However, these are the steps that you'll follow.
4. 1) Open the <%PROGNAMESHORT%> Setup Window. Press either [F6] or select {Settings}{Compute Setup} from the menu.
5. A) For the "Compute Method" select the "Normal" option.
6. B) Set the "Year Length" to 360.
7. C) Click on the [OK] button to close the Window.
8. 2) IMPORTANT: Click on the "Round" button (or press [Ctrl][R] or select "Rounding..." on the {Compute} menu).
9. And make sure "Open Balance" is selected.
10. This setting is what allows you to enter one payment at a time. Any other setting means that <%PROGNAMESHORT%> is going to run a complete schedule for the number of payments entered.
11. 3) Set the compounding to "Monthly" and the annual (interest) rate to 5.25%
12. 4) Set the first row event to "Loan"
13. A) Set the "Date" to 02/16/2009
14. B) Set the "Amount" to $5,250.00
15. C) Set the "# Periods" to 1 to indicate that there is one loan being made.
16. Generally speaking the next step is to calculate the amount of the regular payment if you don't already know what the payment is going to be. For this example, we'll assume that the payment has not yet been determined. If the payment has been agreed to, you can skip to step #7. The borrower has agreed to pay the loan back in 24 equal payment due at monthly intervals. What is the payment amount?
17. 5) Click on the second row and set the event to "Payment".
18. A) Leave the "Date" set to 03/16/2009.
19. B) In the "Amount" column type "U" for "Unknown".
20. C) Set the number of periods to 24.
21. D) Set Frequency to "Monthly". (The "End Date" will be 02/16/2011.)
22. Your screen will now look like this:
23. Calculate the regular payment amount or use any agreed to amount.
24. Click on the "Calculate" button. The regular scheduled payment will be $230.91. (Assuming all of the many options are set the same as we have them set. See example 1 for details.)
25. Now we are ready to start recording payments as they are received.
26. 6) Since we calculated the payment amount assuming 24 payments, we need to clear row #2.
27. A) Right click on row #2.
28. B) Select "Delete" from the pop-up menu. This will clear the 2nd row.
29. 7) The 1st payment is received on time. Click again on row #2.
30. A) Select "Payment" for the event.
31. B) Leave the date set to 03/16/2009.
32. C) In the "Amount" column enter $230.91.
33. D) You are receiving one payment. Enter a "1" under "# Periods".
34. At this point, you may want to save your work. You can save the schedule at any point so when future payments are received, you simply enter them.
35. Assume that the next 3 payments are received on the due date for the amount due. If you saved the payments to a file, you'll want to open the file before entering the next payment.
36. 8) Repeat these steps 3 times. (This means you'll have received 4 payments including the one above in step #7.)
37. A) Click on the empty row after the last payment.
38. B) Select "Payment" for the event.
39. C) Leave the date set by C-Value!.
40. D) In the "Amount" column enter $230.91.
41. E) You are receiving one payment. Enter a "1" under "# Periods".
42. Your screen will now look like this after the payment on 06/16/2009:
43. So far, all payments have been received for the amount due and on the due date.
44. Let's check out the loan balance after these 4 payments are made.
45. 9) Click on the "Amortization Schedule" tab.
46. To see the balance and view the schedule click on the "Amortization Schedule" tab.
47. This will cause the loan schedule to be displayed to date. You'll see each payment and how the principal and interest was applied. Also, notice that after the 4th payment the balance is $4,412.77.
48. The loan balance as of June 16th 2009 is $4,412.77.
49. Click on the "Cash Flow Data" tab to continue entering payments.
50. The borrower is reliable and not only does he pay the 5th payment early, he also pays an extra $100.00. To record this payment:
51. 10) Click on the sixth row. Set the event to "Payment"
52. A) Set the date to 07/10/2009
53. B) Set the "Amount" to $330.91. (Includes the extra $100.00.)
54. C) Set the "# Periods" to 1.
55. So much for the debtor being reliable. Not only does he miss the next payment, he also under pays by $50.00.
56. 11) Click on the seventh row. Set the event to "Payment"
57. A) Set the date to 09/16/2009
58. B) Set the "Amount" to $180.91.
59. C) Set the "# Periods" to 1.
60. After making 4 regular payments, as well as one early payment with an extra $100.00, missing a payment and making a payment that is short by $50.00, your cash flow data screen will look like this:
61. A missed payment.
62. NOTE: It is not necessary to enter a '0.00' for the skipped payment. It is done just as a matter of record keeping. Doing this, explicitly acknowledges that a payment was missed. It also forces the balance to be calculated on the amortization schedule as of the date of the missed payment.
63. Note that interest is being calculated through 8/16/2009 and it is being added to the balance.
64. 12) Your borrower is in need of some additional cash. You agree to lend it to them and add it to the loan balance.
65. A) Click on the empty row after the last payment. This will be row 9.
66. B) Select "Loan" for the event.
67. C) You will make the funds available on October 1st, 2009. Enter 10/01/2009 in the Date column
68. D) In the "Amount" column enter the new loan amount: $1000.00.
69. E) You are making one loan. Enter a "1" under "# Periods".
70. 13) Because of the new loan amount, you want to calculate a new payment amount. The borrower has agreed to pay the loan off in 18 more payments.
71. A) Click on the empty row after loan just entered.
72. B) Select "Payment" for the Event.
73. C) The payments will continue to be due on the 16th of each month. Set the "Date" set to 10/16/2009.
74. D) In the "Amount" column type "U" for "Unknown".
75. E) Set the number of periods to 18.
76. F) Set Frequency to "Monthly"
77. G) Click the "Calculate" button. The new payment will be $286.79.
78. Before clicking "Calculate", your screen will look like this if you've been following along:
79. To calculate new payment amount enter anticipated number of remaining payments.
80. 14) The borrower pays the full payment amount, however he does pay two days late.
81. A) Click on the last row (row 10)
82. B) Change the Date column to 10/18/2009
83. C) The payment amount has been calculated. Since the full payment is paid, leave the amount as it is. $286.79.
84. D) Only one payment is being made. Change the "18" to "1" in the "# Periods" column.
85. One payment paid 2 days late.
86. 15) Continue to enter payments (and loan advances) as they are received until the loan is paid off. Remember, you may enter "0.00" payment amounts on any date to calculate the balance as of the date entered.
87. NOTE: You can easily send the borrower a copy of the amortization schedule. You may export the schedule to Microsoft Word, Excel, PDF or other common file types. From there, you can attach the file to an email.
88. You can also quickly export to Word or Excel from the Amortization Schedule tab.

Back to the online Time Value of Money Calculator.