Remember Me?

#### Menu

#1
December 29th 05, 06:58 PM posted to microsoft.public.excel.worksheet.functions
 VRhodes Posts: n/a
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

#2
December 29th 05, 07:06 PM posted to microsoft.public.excel.worksheet.functions
 JR Posts: n/a
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

#3
December 29th 05, 09:42 PM posted to microsoft.public.excel.worksheet.functions
 VRhodes Posts: n/a
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post cassidy Excel Discussion (Misc queries) 1 July 24th 05 01:09 AM Tony Williams Excel Discussion (Misc queries) 4 July 7th 05 01:10 PM rad Excel Discussion (Misc queries) 0 February 3rd 05 06:05 PM Fred Smith Excel Worksheet Functions 0 January 6th 05 02:33 AM McCarthy_MF Excel Worksheet Functions 0 December 9th 04 09:45 PM

All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017