Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
I have racked my brain to try and figure this out. I have a worksheet with
the following I am having trouble with the ROUNDDOWN part of formula in Columna J. Col E: Monthly Pmt Col G: Total to be paid back Col H: Amt Pd so far =IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30) ) Col I: Balance =IF(H30="","",G30-H30) Col J: Pmts made =IF(TODAY()<$Q30,0,IF(TODAY()$R30,ROUND((($R30-$Q30)/30) +1,0),ROUNDDOWN(((TODAY()-Q30)/30),0))) Col K: Pmts remaining =IF(M30="N",ROUNDUP(G30/E30,0)-J30,"")) Col Q: Start Date Col R: End Date The formula shown shows the correct value in Column J for example 1, but not for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date but Col J does not equal 1 until 30 days after the start date. EXAMPLE 1: Pmt = $250 Total To Be Paid: $23,227.19 Start Date: 9/20/02 Pmts Made (as of 12/20/08) = 76 Pmts Remaining (as of 12/20/08) = 17 Example 2: This is a dummy example I am using to try and fix the problem Pmt = $500 Total to be Paid: $5,000 Start Date = today's date (whatever date that is) I want the Pmts Made (Col J) to equal 1 on the start date and to increase by one each month after that. I can't figure out why the formula works for Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS MADE in Example one goes to 77, instead of staying at 76. NOTE: These are not calculation that involved knowing interest rates, but simply the TOTAL amount to be paid. Your help is GREATLY appreciated. Les |