PMT calculator

[carrie08]

Can someone please tell me how to enter a mixed cell refrence? I'll trying to
calculate a monthly payment schedule based on the years and loan amount.
These cells would be D9 and C10. I'm guessing it has to also be entered with
the same infor in cell D10 where the =pmt (rate, nper, pv) are. My formula is
=-PMT($E5/$E6, $e6*10, 200000). When I put mixed reference(D$9,$C10), in
also, I receive an error in my formula. I have to have the absolute reference
to E5 and E6, which are the interest(6.5%) and compounded rate (12), so I
have that part right. My formula will not copy to the rest of the schedule
because of this mixed reference. Please help!!!!

 

[Bernard Liengme]

Your message is a bit confusing.
Does this work: =-PMT($E5/$E6, $E6*10, 200000)
Note that you are NOT using absolute but mixed referencing - only one $ for
each cell reference.

What cell is the formula in? Now tell us how you want to copy this formula
If you copy if down the column by one row it will become
=-PMT($E6/$E7, $E7*10, 200000)
if you copy it across a row it will remain unchanged
best wishes

 

[JLatham]

Carrie, $E5 and $E6 are mixed cell references, as are D$9 and C$10

I'll try to explain you have 3 types of cell references:
Absolute: neither the column reference nor the row reference are allowed to
change. The $ tells Excel not to change a reference. So an absolute
reference looks like $E$5 or $D$10

Relative: both the column reference and the row reference are allowed to be
changed by one of a couple of factors: you insert cells/rows/columns above/to
the left of where the referenced cell is on the sheet. So if you have a
formula in cell A1 like =D9 and you insert a new row anywhere above row 9,
that formula would become =D10.

Mixed: You can allow the column reference to change as you fill the formula
left/right across the worksheet, but keep the row from changing as: D$9. So
as you drag a formula with that reference in it left/right across the sheet,
the column reference would change, but the row would not as you drag it
up/down on the sheet.
You can allow the row reference to change as you fill the formula up/down
across the worksheet, but keep the column reference from changing as: $D9.
So as you drag a formula with that referrence in it left/right across the
sheet, the $D9 would remain the same, and if you drag it up/down the sheet,
the $D still remains $D, but the row reference would change.

In looking at your description of the problem, I think you need to reference
D$9 and $C10 as absolutes, like $D$9 and $C$10

Hope that helps you with this some.

 

[carrie08]

Getting closer, but not there yet. Changed $E5 and $E6 to absolute values,
and now, as least my chart is filling up with the same number. My header of
D9 has 10 yr in it, and goes across to 30 yrs, in increments of 5. My row C10
has $200,000 in it and goes down to $300,00 in increments 10,000. When I try
to Autofill from cell D10, the same number of $2,270.96 fills across and down
my chart. How do I put D$9 and $C10 into my formula????

 

[JLatham]

with 6.5% in E5 and 12 in E6, and
with 10 in D9 and incrementing across as 10, 15, 20, 25, 30
and with 200000 in C10 and incrementing down as
200000
210000
220000
....
300000

At D10 put this formula:
=-PMT($E$5/$E$6,$E$6*D$9,$C10)
now that should fill across and down properly. I get 2270.96 in cell D10
and down in the lower right hand corner at H20 I get 1896.20. And in H20 the
formula has become =-PMT($E$5/$E$6,$E$6*H$9,$C20) through the fill action.

 

[carrie08]

Thank you so much. My spreadsheet is now correct with the editing of the
formula. I'm new at this training, and I'm sure that I will be asking other
questions as I go through my Excel program training.

 

[carrie08]

I here once again asking for help. I am trying to use a nested IF and AND
formula to calculate the vaction days for full time employess with different
service years. I need to nest 3 different answers. Here is my formula that
keeps getting rejected:

=IF(And([Status]="FT",[Years Employed]>=4,15,IF([Years
Employed]=2>4,10,IF([Years Employed]=1<2,5,0))).

What am I not doing right??

 

[rvtrails]

Hello: Can you expalin to me why in the lower section of this email you are
using a minus between the = and PMT (i.e. =-pmt...) I have never seen that
before and can not find information about it.
Thanks!

I here once again asking for help. I am trying to use a nested IF and AND
formula to calculate the vaction days for full time employess with different
service years. I need to nest 3 different answers. Here is my formula that
keeps getting rejected:

=IF(And([Status]="FT",[Years Employed]>=4,15,IF([Years
Employed]=2>4,10,IF([Years Employed]=1<2,5,0))).

What am I not doing right??
--
kae

with 6.5% in E5 and 12 in E6, and
with 10 in D9 and incrementing across as 10, 15, 20, 25, 30
and with 200000 in C10 and incrementing down as
200000
210000
220000
...
300000

At D10 put this formula:
=-PMT($E$5/$E$6,$E$6*D$9,$C10)
now that should fill across and down properly. I get 2270.96 in cell D10
and down in the lower right hand corner at H20 I get 1896.20. And in H20 the
formula has become =-PMT($E$5/$E$6,$E$6*H$9,$C20) through the fill action.

 

[Bill Sharpe]

Hello: Can you expalin to me why in the lower section of this email you are
using a minus between the = and PMT (i.e. =-pmt...) I have never seen that
before and can not find information about it.
Thanks!

The minus sign merely changes the sign of the formula result. I believe
in this case you'll get a positive number when using the minus sign,
even though that seems counter-intuitive.