Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 29th 05, 06:58 PM posted to microsoft.public.excel.worksheet.functions
VRhodes
 
Posts: n/a
Default 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   Report Post  
Old December 29th 05, 07:06 PM posted to microsoft.public.excel.worksheet.functions
JR
 
Posts: n/a
Default 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   Report Post  
Old December 29th 05, 09:42 PM posted to microsoft.public.excel.worksheet.functions
VRhodes
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
WANTED: Excel template for loan payment record with random/irregular payments cassidy Excel Discussion (Misc queries) 1 July 24th 05 01:09 AM
Keeping track of loan payments and interest Tony Williams Excel Discussion (Misc queries) 4 July 7th 05 01:10 PM
How do you set up a loan using the loan calculator w/odd payments. rad Excel Discussion (Misc queries) 0 February 3rd 05 06:05 PM
How to calculate total interest on 12 month loan with early payments Fred Smith Excel Worksheet Functions 0 January 6th 05 02:33 AM
Loan amortization schedule with bi-monthly payments McCarthy_MF Excel Worksheet Functions 0 December 9th 04 09:45 PM


All times are GMT +1. The time now is 07:15 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017