Thursday, March 7, 2013

Excel Problem - Recurring Subtraction

Here's the problem.

My maid Jaya has borrowed Rs. 2000/- from me to pay her daughter's school fees. She tells me to deduct Rs.200 from her salary every month. I want a spreadsheet that shows, the total amount she has borrowed, and the balance she has to repay as I deduct Rs. 200 every month.

This is the easiest way I can go about doing it.

Step 1: I've entered the month, the amount borrowed, and the amount she said I could deduct from her salary in the columns A, B, and C.

Step 2: For D1, I've assigned the value '=B1-C1'; Rs.1800

Step 3: Obviously, the value for B2 is ' =D1' as by Feb the money she has to pay me back is Rs.1800 which is same as the value in the cell D1.

Step 4: Clearly, she'll be repaying me over a period of 10 months. When I place the mouse pointer on the spot shown in the picture above, the pointer changes to plus. I've dragged the handle up to row 10 to fill in the months. You can see the result in the pic below. 

I've applied the same technique to fill in the values in column C.

Step 5:  If you've done exactly what I've so far, your screen will show what you see below. 

Negative values? Not to worry. When I do the same with column B, I'll get what I set out to do.

By October, you can see that she has paid all her dues.