How to program sheet for simple loan payment?

In My Humble Opinion
1

On Google sheets (which is the same as excel so I’m told).

I’ve figure out the “=pmt” part, but how do I get it to display the new balance each time a payment is made?

So the loan is 10,000.00.
Payment is 110 biweekly.

I’ve tried programming the balance cell as follows: “=10,000.00-110”* or more precisely: “cell the loan amount is in-cell the payment amount is in”.

But that’s not working.

ETA: Loan is interest free.

Thanks

2

A= previous balance
B= payment
C = current balance

A1 = 10000
B1 = 110
C1 = A1-B1

A2 = C1
B2 = 110
C2 = A2-B2

A3 = C2
B3 = 110
C3 = A3 -B3

3

Try recursion
A1 ‘Payment’
B1 ‘Balance’
A2 = 1
B2 = 10000
A3 =A1+1
B3 =B1-110

Copy row 3 and paste as far as you need until it’s paid off
If you need a payoff table that uses interest that is pretty straightforward.

4

Thanks. That did the trick. And easy too! :slight_smile:

One more question: Is there a way to make the dates fill in two weeks apart? Or do I have to enter that manually? Last question, I promise.

5

NM, I figured it out. Thanks again guys.

6

Just so everyone knows the solution to the date problem, Excel uses Julian days. You can see this by inputing a day and changing the format to “General”. So for example to add 7 days to the date in C2 simply type in =C2+7 then format to a date as necessary.

Ask as many questions as you like.