Armando Silva said:
Hi, I am just started using Excel 2002 and love it,
I am using it mostly for my personal finances.
I would like to know if someone has already done this and if
so, if they could show me how.
I have several credit cards and would like to know how long
would it take me to pay off the balance in months (or years ;( )
with data like APR%, Monthly payment, Total Balance.
I would like to show something like this:
Card Name : Total Bal : Monthly Pay : APR% : Length to Pay :
Discover 800.00 50.00 9.99% 34 Months
Does anyone know the formula to this ?
Thanks in advance !
Armando,
It sounds like the NPER function could help you out. Here is the info
from the help file:
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
NPER
Returns the number of periods for an investment based on periodic,
constant payments and a constant interest rate.
Syntax
NPER(rate, pmt, pv, fv, type)
For a more complete description of the arguments in NPER and for more
information about annuity functions, see PV.
Rate is the interest rate per period.
Pmt is the payment made each period; it cannot change over the life
of the annuity. Typically, pmt contains principal and interest but no
other fees or taxes.
Pv is the present value, or the lump-sum amount that a series of
future payments is worth right now.
Fv is the future value, or a cash balance you want to attain after
the last payment is made. If fv is omitted, it is assumed to be 0 (the
future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period
Examples
NPER(12%/12, -100, -1000, 10000, 1) equals 60
NPER(1%, -100, -1000, 10000) equals 60
NPER(1%, -100, 1000) equals 11
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
Just make sure that the interest rate and payment frequency that you
use match up.
Excel has built in functions for solving for any of these values
(present value, interest rate, payment, number of payments, future
value). So you could also answer questions like, "how much would my
monthly payment have to be if I wanted to pay this off by the end of
the year?" or "what's my balance going to be after my sixth payment?"
If you're feeling up to it, you could do a search for interest theory,
annuities, etc. to learn about how these things are related.
Hope this helps,
Mike
