Loan repayment calculator

[danielexcel]

Hi,

I'm brand new to this forum and fairly new to excel. I'm building a property investment model and one stage of the model is a loan repayment calculator. I have built it and it runs perfectly. However, I want to add a feature that allows you to see how many days you could save from the total of the loan if you make a one extra repayment.

Basically I have tried the following formula but I would have to type it out over 300 times and I don't think excel allows and IF function to be used that many times in the one formula. Basically what I'm trying to achieve is, if one cell in one collum equals zero what is the value of the corresponding cell two collums over.

ie

=IF(D10=0,B10,"")IF(D11=0,B11,"")IF(D12=0,B12,"") etc

Any help is greatly appreciated.

Cheers

 

[Jon von der Heyden]

Do you want to sum the corresponding values, or just get the first occurrence where D is 0, or perhaps the last occurrence?

 

[Jon von der Heyden]

As a guess:

=SUMIF(D10:D310,0,B10:B310)

 

[dmt32]

Hi,

I'm brand new to this forum and fairly new to excel. I'm building a property investment model and one stage of the model is a loan repayment calculator. I have built it and it runs perfectly. However, I want to add a feature that allows you to see how many days you could save from the total of the loan if you make a one extra repayment.

Basically I have tried the following formula but I would have to type it out over 300 times and I don't think excel allows and IF function to be used that many times in the one formula. Basically what I'm trying to achieve is, if one cell in one collum equals zero what is the value of the corresponding cell two collums over.

ie

=IF(D10=0,B10,"")IF(D11=0,B11,"")IF(D12=0,B12,"") etc

Any help is greatly appreciated.

Cheers

have a look at this site Free Loan Calculators for Excel

They have number of examples you can download for free.

Dave

 

[danielexcel]

Just the first time D=0 because depending on the data input D could equal zero fifty times after the intial

 

[danielexcel]

Yeah I've looked at that site before, I kind of want to build my own one from scratch as I am studying towards an economics degree at uni and if I need model designing skills anyway.

Thanks though mate!

 

[danielexcel]

As a guess:

=SUMIF(D10:D310,0,B10:B310)

Just the first time D=0 because depending on the data input D could equal zero fifty times after the intial

 

[Jon von der Heyden]

OK, exact match VLOOKUP returns 1st match.

=VLOOKUP(0,CHOOSE({1,2},D10:D310,B10:B310),2,FALSE)

 

[danielexcel]

OK, exact match VLOOKUP returns 1st match.

=VLOOKUP(0,CHOOSE({1,2},D10:D310,B10:B310),2,FALSE)

Works perfectly mate thanks for all your help!