Add insurance to loan payments
 

I need to add insurance premiums to equipment loans, if selected by a
customer. We offer 3 kinds of insurance. Insurance inputs are in cells as
follows:
Credit Life in C17:G17
Disability in C18:G18
Physical Damage in J16:J18
Then I have interest rates in B20:K20. There are only 5 rates, 2 merged
cells per rate, eg, B20:C20 is 7.0%, etc, for terms of 2-3-4-5-6 years
I want to calculate a loan payment ONLY if one or more insurance rates are
selected, AND there is an interest rate put in. For example, if there is an
insurance premium selected, and I put an interest rate in only 2 of the 5
(for a 4 year or 5 year loan), I want to calculate only those 2 payments.
I have no trouble calculating payments, just how to set up the multiple
criteria. I have a separate section where I calculate the same loan without
insurance added.
I have tried IF and IF/OR formulas, but haven't got it right.
Any ideas?
Thanks in advance
VRhodes

Add insurance to loan payments
 

....have you tried a nested "IF"? If(**,*,If(**,*,If(**,*,))*)

"VRhodes" wrote:

I need to add insurance premiums to equipment loans, if selected by a
customer. We offer 3 kinds of insurance. Insurance inputs are in cells as
follows:
Credit Life in C17:G17
Disability in C18:G18
Physical Damage in J16:J18
Then I have interest rates in B20:K20. There are only 5 rates, 2 merged
cells per rate, eg, B20:C20 is 7.0%, etc, for terms of 2-3-4-5-6 years
I want to calculate a loan payment ONLY if one or more insurance rates are
selected, AND there is an interest rate put in. For example, if there is an
insurance premium selected, and I put an interest rate in only 2 of the 5
(for a 4 year or 5 year loan), I want to calculate only those 2 payments.
I have no trouble calculating payments, just how to set up the multiple
criteria. I have a separate section where I calculate the same loan without
insurance added.
I have tried IF and IF/OR formulas, but haven't got it right.
Any ideas?
Thanks in advance
VRhodes

Add insurance to loan payments
 

Thanks JR, I have tried several combinations. My trouble is writing a formula
that will test the insurance inputs, and the interest rate. I don't know how
to write an IF formula using both OR and AND functions, but that's what I
think I need.
If "any input cell" AND if "interest rate" is what I want, then calculate
payments only on the loans with a rate set up. The other issue is that the
rest of the formula is quite long because of the PMT elements. I have to add
several insurance calculations to the basic payment, so it's a long formula
(to me! <bg).
Here's what I've got so far, and it works, but no way to test for interest
rate in it yet.
=IF(OR(Credit
Life0,Disability0,PhysDam0),PMT(B20/12,24,-LoanAmount-CLIPrem-DisPrem-PDIPrem,0,0),"")


Thanks for your help

VRhodes
"JR" wrote:

...have you tried a nested "IF"? If(**,*,If(**,*,If(**,*,))*)

"VRhodes" wrote:

I need to add insurance premiums to equipment loans, if selected by a
customer. We offer 3 kinds of insurance. Insurance inputs are in cells as
follows:
Credit Life in C17:G17
Disability in C18:G18
Physical Damage in J16:J18
Then I have interest rates in B20:K20. There are only 5 rates, 2 merged
cells per rate, eg, B20:C20 is 7.0%, etc, for terms of 2-3-4-5-6 years
I want to calculate a loan payment ONLY if one or more insurance rates are
selected, AND there is an interest rate put in. For example, if there is an
insurance premium selected, and I put an interest rate in only 2 of the 5
(for a 4 year or 5 year loan), I want to calculate only those 2 payments.
I have no trouble calculating payments, just how to set up the multiple
criteria. I have a separate section where I calculate the same loan without
insurance added.
I have tried IF and IF/OR formulas, but haven't got it right.
Any ideas?
Thanks in advance
VRhodes