Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multi-lookups on variable data
I'm trying to design a formula that will calculate interest on a daily basis
for a loan which has a floating interest rate based on a Bank's Prime Lending Rate. I have a separate worksheet which contains a table with Lending Rate Effective Date (A!A2:A2000), End Date (A!B2:B2000), Lending Rate(A!C2:C2000), Surcharge (A!D2:D2000) and Charge Rate (A!E2:E2000). The Table and ranges have been named. The Interest Calculation formulas cannot use named ranged as there will be multiple worksheets in the notebook; one worksheet for each loan. Each Loan sheet is based on a template with 3 key sections; * the Payment Anniversary Dates (B!E15:H15) * the Payment Received Dates (B!C23:C29) * the Interest Calculation Period: Period Start (B!B36:B45), Period End (B!C36:C45) The Period Start formula reads IF(D36="","",IF(OR(D36+1$G$14,D36+1TODAY()),"",D 36+1)) I need a formula that will populate the Period End Date as the lesser of i) the lookup of the Period Start Date in the Lending Rate End Date ii) the lookup of the Payment Received Date that is less than the lookup of the Period Start Date in the Lending Rate End Date iii) the of the Payment Anniversary Date for the period being calculated. Any ideas would be greatly appreciated. -- .. . . Cheers C.Pflugrath |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
Getting data from another workbook based on variable | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
What variable do you insert for X-cel if there is missing data? | New Users to Excel |