Hi everyone - first time poster here and as probably most posts here are
I am looking for help.

Excel does not recognise €0.00 as the value 0. I need this for the
following. Basicaly I have created a loan amortisation sheet breaking
down each monthly payment into repayment amount / principal / interest
etc etc. I am using the PMT formula to calculate each monthly payment
as it goes.

=-PMT(D11/12,$I$4*12-A10,C11)
D11 being a cell referencing the interest rate / 12 to make monthly.
$I$4*12-A10 is calulating the payments left I4 is the original loan
term less the the number of payments made A10 to give how many are left
) and C11 is the balance left on the loan.

I hope this is easy enough to follow :)
It works a dream when I create exactly enough rows for the amount of
payments. My problem occurs when I change the original loan term in I4.
I would like to be able to change the loan term and have the appropriate
rows of calculations disappear / go blank when not needed. ie if I
change it from a 35 year term to a 30 year one that the last 60 rows of
calculations disappear. If I decrease this term I get a #Div/0! error
across the formulas for the time period past the new lesser loan time.
I am assuming this is within the PMT formula when the principle is at 0
as the loan has been paid off.

I tried to find a function that would check wether or not a value was
at 0 elsewhere and if so to return 0 itself but if the value elsewhere
was not at 0 to include that figure within a function of the original
cell.

I tried to use the IF function and insert the pmt function within it.
ie to see if C11 was equal to 0 and if it was to return 0 as a value
for the function but if it did not equal 0 to calculate the original
PMT function. =-PMT(D11/12,$I$4*12-A10,C11). but I cannot make the PMT
functtion work within one of the IF true or false options.

Furthermore it also appears that even in a simple IF function excel
does not recognise €0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and
A1 contains €0.00 it gives the value of no. If I put it as
=IF(A!=€0.00,"yes","no") it is invalid and finally for
=IF(A!="€0.00","yes","no") it again shows no.

I am looking for any way to either get excel to recognise a zero in
currency to be the same as 0 or to get a PMT function active within IF
( preferably both as I suspect I need both of these at the same time to
do this for me )

I really hope that you can follow the above - it makes more sense in
your own mind than when you try to explain it.

Many thanks in advance for any advice.


--
CmTaz
------------------------------------------------------------------------
CmTaz's Profile: http://www.excelforum.com/member.php...o&userid=32169
View this thread: http://www.excelforum.com/showthread...hreadid=519180