Excel Spreadsheet problem

Question

Hello! So I'm having a excel issue. It's suppose to automatically fill in data, but I am getting errors when I try to do the thing where you drag it down the rows you want to duplicate/populate. It seems like its changing the math I want done in the cell. 

This is what I want in the whole "payment" section: =PMT(B2/B4,B3*B4,-B1)

but when I drag it to apply it to more than 1 cell it advances the numbers(changes it from -B1 to B3): =PMT(B4/B6,B5*B6,-B3) (I guess it's changing all the cell ID's as well)- then it spits out a #VALUE! or #DIV/0! error. In the picture I provided I had to manually put in the "$933.40" payment for those first 3 rows. 

So it should be this: =PMT(B2/B4,B3*B4,-B1) for that whole row but it's changing the formula when I drag it down.

https://imgur.com/34cVPie

Any advice? What am I doing wrong?

Thanks!

Answer 1

Hi frza45

You just need to change the cell reference type in the original formula then drag down

In any part of that formula where you don't want a cell reference to change, make this change for example if you don't want B3 to change as you drag down, change B3 to $B$3 and so on in your formula

Answer 2

Ok, great! That worked! But now I need to calculate interest, total interest and balance off the data. I am new to excel so if you don't want to tell me maybe you could show me a tutorial that would be good for this? I'm confused, the interest rate and the balance should be going down. Basically, how do I get something like the total interest rate to add the previous and current interest rates so I get the running total? Then have it all display automatically when I drag the column? 

Thanks!