Subtracting Loan payment in Years

**travistea** · 05-20-2013, 07:13 AM

Hi members! I'm just a couple weeks into excel and am having a hard time with a formula.

I want to subtract one cell from another by one (years) until it reaches zero.

This is a budget for a hypothetical business. The cell i want to subtract from is the loan payment years. i.e add $x,xxx.xx (loan payment) to fixed expenses until the number of years hits zero.

I hope this is enough info for the gurus out there.

Thanks!

**BenMiller** · 05-20-2013, 07:18 AM

Maybe try the PMT function, which will calculate basic loan dates.

**travistea** · 05-20-2013, 07:55 AM

I'll try to elaborate a little.

I want to track the costs of manufacturing a product.

I have 2 sheets "data" and "spreadsheet"

In the data sheet, I have my variable and fixed expenses divided by the quantity I want to produce.
=(variable expenses + fixed expenses)/quantity

That gives me price per unit.

Actual formula is: =(E12)+(SUM(C17,C26,C34,C40,C44))/G12
E12 =Variable expenses
(SUM(C17,C26,C34,C40,C44)= Fixed Expenses
C17= Monthly Loan Payment
C26,C34,C40,C44= Other fixed costs
G12= Quantity Produced

I created a basic PMT calculator for the loan.

The spreadsheet is 10 years with monthly totals.

I have in my PMT calculator the loan lasting 8 years.

How do I get the loan to stop adding the monthly payments after the loan period ends?

Can I subtract 1 from loan period cell for each year until it equals 0? or 1/96 per month until it reaches 0 months?

Thanks guys

...one other thing, I could calculate the monthly payment for the time period and insert that but I am trying to keep the sheet light and clean and would rather do it via the formula bar

**BenMiller** · 05-20-2013, 09:12 AM

Where are you pulling time values from? Your formula is bound by number, not time. I know you said you have monthly totals, but is there a cell next to each calculation showing a date?

**travistea** · 05-29-2013, 12:54 PM

Hey guys sorry for not getting back. I needed a vacation!

If anyone is still interested...

there is no cell showing a date, just a set amount of years. I want to subtract the years of payments (8) from my 10 year spreadsheet. So I would be subtracting the total loan payment amount for 8 years and then I would like it to stop subtracting from the cell with the years in it and continue on subtracting other expenses. I could do a spreadsheet and map out the payments by month for 8 years, but was just trying to make a simple user friendly spreadsheet.

Thanks guys