credit calculations
Thanks Guys!
" wrote
in message ...
"Danny J" wrote:
e.g. I borrow £1000 on a credit card on 1.2% monthly
interest. I only make monthly payments of 5% of the
outstanding balance. How long will it take me
to clear the debt and what will the interest be?
Longer than would ever be reasonable, based only on
those rules. As a practical matter, you should also stipulate
a minimum payment.
Spreadsheets (Visicalc) were invented, in part, to provide
answers to such "what-if" questions not with plethora of
built-in functions, but by allowing you to model the situation.
Models often give you greater insight into what is happening
with the numbers, allowing you to hone your strategy more
effectively.
I suggest that you contruct the following spreadsheet:
A2: payment number: 1,...
B2: payment date: 2/1/2006, 3/1/2006,...
C2: interest: =F1*1.2%
D2: payment: =ROUND((F1+C2)*5%, 2)
E2: additional payment (blank for now)
F2: balance: =F1+C2-D2-E2
A1 = 0
C1: total interest: =SUM(C2:C250)
F1 = initial balance (1000)
Highlight A2:F2 and drag the lower-right corner down for
as many rows as needed until the balance is reduced to
nearly zero. You should be able to see the problem.
Notes:
1. When we drag the row that way, Excel increments B2
(date) by one. There are several ways to work around
the "problem". If they are not obvious to you, repost here.
2. The formula for C2 is valid at least for C3 and beyond.
It might be valid for C2, as well; alternatively, it might
be zero. It depends on your repayment agreement.
3. I purposely do not round interest and balance, whereas
payment must be rounded. If you choose to round
interest, I would suggest using ROUNDUP(), a worst-case
assumption, unless your lender tells how they calculate
interest (unlikely!).
|