need a loan formula

[doreen]

I have a loan that I want to keep track of every time a make a payment.
I have found a formula for Pay Period-Payment-Interest-Principal and
Balance but I want to be abel to record the date of my payment and have
eveything change to reflecting my balance.
Is this possible?

 

[Norman Harker]

Hi Doreen!

If you want to do things the (accurate) date of payment method, then
you are going to have to swap Dates for Pay Period.

This will require a change to your interest calculation. Instead of
using the effective rate per month for one period on the preceding
balance, you will need to calculate the interest based on the
equivalent daily effective rate for the number of days since the last
balance.

Principal paid might then need re-adjusting, depending upon how you
calculate it. I find that the easiest way is to deduct the interest
calculation from the payment using the argument that all which is not
interest / fees is principal.

Then the running balance changes by deduction of principal repaid.

Such a workbook will more accurately reflect your lender statements
and you could even insert a date in your schedule when the lender
strikes a statement.

Any further help, don't hesitate to ask. And I'd love to see your
finished workbook in any event.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[doreen]

Thank you Norman.
I am not fluent in Excel I just know the basics. I just thought tha
maybe I could get the formula and make it easiler. The spreadsheet
have now gives me the amount that I owe and I just have to insert th
date which has no function--it is just data. My spreadsheet took me al
day to figure out and I had help that I found on the web. (slo
learner )

I really do appreciate your help though and I will print out you
reply.

Thank you
Doree