I need a formula/s that will allow me to take: Start Date, End Date, Payment Amount, then compute from a drop down menu (Weekly, Bi-Weekly, Monthly, Quarterly, or Semi-Annual payments), the Number of Payments and Total Amount Owed. First payment starts on Start Date. Bi-Weekly payments are paid twice a month, on the 1st and 15th of each month. Payments start either on the 1st or 15th of the month, which ever is entered on the Start Date.
The formula/s will be based on drop down selection made.
Example: A1 - Start Date
A2 - End Date
A3 - Payment Amount
A4 - Drop Down Menu (Weekly, Bi-Weekly, Monthly, Quarterly, Semi-Annual)
A5 - Number of Payments(calculated - counting payments starting with Start Date)
A6 - Total Amount Owed (calculated A3 * A5)
--------------------------
----------
----------
----------
----------
---------
Result1: A1 - 2 Jan 2003
A2 - 31 Mar 2003
A3 - $75.00
A4 - Weekly (Selected from drop dowm menu)
A5 - 13 (calculated - counting # weeks starting with 2 Jan 2003)
A6 - $975.00(calculated A3 * A5)
Payments were due on 2, 9, 16, 23, 30 Jan, 6, 13, 20, 27 Feb, 6, 13, 20, and 27 Mar.
--------------------------
----------
----------
----------
----------
----------
Result2: A1 - 2 Jan 2003
A2 - 31 Mar 2003
A3 - $150.00
A4 - Bi-Weekly (Selected from drop dowm menu)
A5 - 5 (calculated - starting with 15 Jan 2003)
A6 - $750.00(calculated A3 * A5)
Payments were due on 15 Jan, 1 Feb, 15 Feb, 1 Mar, & 15 Mar.
--------------------------
----------
----------
----------
----------
----------
-
etc. for Monthly, Quarterly, and Semi-Annual.
Thanks Ahead of time.
Start Free Trial
