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 with each payment having it's own row of information.
=-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. I set it not to view 0 but I can't get ithe unneeded cells to actually return 0 at all. 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.
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 with each payment having it's own row of information.
=-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. I set it not to view 0 but I can't get ithe unneeded cells to actually return 0 at all. 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.
