Solved: Counter Number Non start with 1

[tlchan]

Hi,

I try to number the cell A9 to variable row which depend on cell B4. I manged to copy formula to variable row for column B,C & D according to value in B4. However I'm not able to get counter in cell A9 to start with number 1. It always start with 0 using for next loop.

Any help is appreciated


workbook attached

[tpoynton]

one way
[vba]
For i = 9 To varno
Cells(i, 1).Value = i - 8
Next i
[/vba]

[mohanvijay]

you did not set the value for counter variable so that assumes starting is 0 to solve this
1.write counter=1 after the variable declaration or
2.put the counter = counter+1 line before the line Cells(i, 1).Value = counter

[macropod]

Hi tlchan,

It seems to me your worksheet may have some fundamental issues to resolve regarding financial calculations. Basically, your formulae don't reflect the usual way of calculating interest on loans where the balance dimishes at regular intervals and the interest is only applied to the outstanding balance each month. In effect, your worksheet's 5% interest figure works out at 9.1% on the reducing balance.

Ordinarily, one would calculate the periodic payment using Excel's PMT function. For your worksheet, that would be expressed as:
=-PMT(D$4/12,B$4,C$4,1,0)
in C9, which works out to $428.12/month. The total payments for the loan would thus be this times the number of months in the loan term (ie
=C9*B4, which works out to $5137.43). In older versions of Excel, at least, there is a problem with the PMT function, which means it typically leaves a loan overpaid by about $1.00. You can overcome this by calculating monthly payments with the following formula:
=IF(D$4=0,(C$4+0)/B$4,(C$4*((1+D$4/12)^B$4)+0)/((1+D$4/12*0)*((1+D$4/12)^B$4-1)/(D$4/12)))
In Excel Help File parlance, this is equivalent to:
=-IF(rate=0,(pv+fv)/nper,(pv*((1+rate/12)^nper)+fv)/((1+rate/12*type)*((1+rate/12)^nper-1)/(rate/12)))
If you look up Excel's Help File for the PMT, NPER etc functions, you'll find an explanation for what the various terms in the above formula represent.

Similarly, one would calculate the balance outstanding each month by a formula in D9 like:
=FV(D$4/12,A9,C9,-C$4,0)
and copied down as far as needed. Unlike the PMT function, this one apparently works correctly.

Attached is an updated copy of your workbook with two sheets. Sheet1 applies the various formulae without the need for a macro. Sheet2 (to which the macro now points) uses a different version of your macro to do the worksheet updating.

[tlchan]

Hi there,
Thanks to Mohanvijay & Tpoynton for your assistance. All solution works well. in actual fact the interest calculation I used still used in my country for certain type of loan (we called as flat rate). Anyway special thanks to Macropod of sharing of other calculation method.

Once again thanks for all your assistance provided

[macropod]

Hi tlchan,

Hi there,
in actual fact the interest calculation I used still used in my country for certain type of loan (we called as flat rate).

You can still do that without a macro. The attached workbook sows you how to do that with Sheet1. A modified macro (which I believe is still more efficient than the one you're using) is also included and acts on Sheet2, so you can see how the two solutions compare.