Loan Statement Tracking

[loftus49]

No need to respond to this. I figured it out.

Here's the next project. I'm doing a spreadsheet whereby she can track her Monthly loan activity. Here's the sample detail.

Balance Forward = $69,174.05
Beginning ANNUAL PERCENTAGE RATE = 2.49% Daily (Periodic Rate .00006803)
Automatic Payment (total is equal to 1% of Balance Forward) is calculated by subtracting the APR above from the total Automatic Payment and applying the remainder to reduction of Principal. Example, Automatic Payment = $691.74 (Principal $554.37, Interest $137.37)
Finance Charge (Interest) Accrued for the month = $145.63 (2-15-12 thru 3/16/12)
Principal Balance = $68,619.68

Current Statement Balance = $68,765.31
minus
Current Fee and Finance Charge = $145.63
plus
Previous Unpaid Fees and Finance Charges = 0
Principal Balance = $68,619.68


Clear as Mud?

Now, she has a son who shares this 2nd mortgage loan account with her. Both can "withdraw" from time to time and both can make extra (beyond the 1% auto payment) deposits from time to time. She "calculates" what portion of the imposed interest is her's and what portion is her son's. She uses the following method: My balance (from sheet which is 39,164 from March) divided by current equity balance (69,175 on sheet) = %, x actual month interest = $$ my portion interest for the April 10 bill.

Example 39,164 divided by 69,174 = 57% x $146 = $83

I'm attaching a sheet that shows the heading info for the information she currently records (on paper).

This project is not for the faint of heart. But ANY help/guidance/suggestions/templates/name calling would be appreciated. Thanks.