Home 
Search 
Today's Posts 
#1




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 23456 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 
#2




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 23456 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 
#3




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,LoanAmountCLIPremDisPremPDIPrem,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 23456 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
WANTED: Excel template for loan payment record with random/irregular payments  Excel Discussion (Misc queries)  
Keeping track of loan payments and interest  Excel Discussion (Misc queries)  
How do you set up a loan using the loan calculator w/odd payments.  Excel Discussion (Misc queries)  
How to calculate total interest on 12 month loan with early payments  Excel Worksheet Functions  
Loan amortization schedule with bimonthly payments  Excel Worksheet Functions 