credit calculations

[msnews.microsoft.com]

Hi folks,

I am wondering how I can calculate the interest paid on a credit card and
how long it will take me to pay off my debt if I make minimum payments of
5%.

Many thanks,

Danny

 

[Niek Otten]

Look at the NPER() function.
Make sure you define correctly what you mean by "5%". 5% of what? Initial
debt? Outstanding amount?

Also look at the template and instructions that can be downloaded here:

http://office.microsoft.com/en-us/assistance/HA010346401033.aspx

 

[Danny J]

Dear Niek Otten,

Thank you for your help.

The problem with NPER is that it looks like it requires constant payments.
whereas I am looking at 5% of the outstanding balance.

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?

Can I use NPER?

Danny

Thank you for

 

[Peo Sjoblom]

I don't think any built in function will do this, Norman Harker wrote a
functions for this
First in the workbook where you do this press Alt + F11, click insert module
and paste in

Function CardNPER(Loan As Double, LoanRate As Single, RepayPercent As
Double, RepayMinAmount As Single)
Dim SuckerRepayment As Double
Dim Interest As Double
Dim NeverNever As Integer
Do Until Loan >= 0
If RepayPercent * -Loan >= RepayMinAmount Then
SuckerRepayment = RepayPercent * -Loan
Else
If RepayPercent * -Loan > -Loan Then
SuckerRepayment = -Loan + (RepayPercent * -Loan)
Else
SuckerRepayment = RepayMinAmount
End If
End If
Interest = Loan * LoanRate
Loan = Loan + SuckerRepayment + Interest
NeverNever = NeverNever + 1
Loop
CardNPER = NeverNever
End Function

Make sure you don't get any line wrap press alt + Q to close the VBE then
use it as in

=CardNPER(-1000,1.2%,5%,10)

where -1000 is the starting balance, 1.2% the rate, 5% the payment and 10 is
the minimum value you need to pay per month (10 pounds?) According to that
it would take 65 months with 10 as minimum value but with 1 as a minimum
vale it would take 124 months

If you really find this useful you can open a new workbook, repeat the paste
into VBE, save the workbook as
an add-in (*.xla), then check it under tools>add-ins and restart excel and
it will always be available for any workbook It obviously is not a good way
to pay off a loan thus parts of Norman's code like

Dim SuckerRepayment As Double

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

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!).

 

[Danny J]

Thanks Guys!

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!).