Trying to get a new formula


A

Armando Silva

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 !
 
Ad

Advertisements

M

Mike

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
 
Ad

Advertisements

A

Armando Silva

Mike said:
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.
Hope this helps,

Mike

Thanks Mike ! I will try it.

Armando.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top