amount to borrow function

**kingcomposing** · 05-08-2008, 03:58 AM

what function can i use to calculate how much i should borrow when i know what the interest rate is, the length of the loan and the max amount i can pay back per month???

**mudraker** · 05-08-2008, 04:01 AM

kingcomposing

Welcome to Exceltip forum

Please take a couple of minutes and read the forum rules then please edit your thread title by following the instructions in the rules (Rule 1)

An Announcement thread that covers the rules is at the top of each forum or you may use the link below in my signature.

**kingcomposing** · 05-08-2008, 04:49 AM

thanks for the warm welcome mudtraker. anyone able to help?

**deadlyduck** · 05-08-2008, 05:26 AM

I think you would need to use a combination of the PMT function and Goal seek.

If you enter your data as follows:

A1: Annual interest rate
B1: Number of years of the loan
C1: Loan amount- I know you don't know this yet but just pick a figure.

In cell A3, enter the following:

=PMT(A1/12,B1*12,C1,,)

This will calculate the monthly repayment (which will probably be way off your maximum possible amount).

Now you use Goal seek to see how much you could borrow:

Click on the answer you've computed above.
Click Tools | Goal seek
In the dialog box which appears, enter the actual monthly amount you can pay in the 'To value' field.
Click on the 'By changing cell' field and then click on the loan amount you guessed (cell C1).

Excel will compute the loan amount.

**kingcomposing** · 05-08-2008, 06:11 AM

thanks but why am i getting a negative figure?

**deadlyduck** · 05-08-2008, 07:35 AM

Ignore the minus symbol- the value you get is the amount you can borrow.

It's negative because it's a liability- you owe it to the bank.

**rbanks** · 05-08-2008, 05:14 PM

Originally Posted by deadlyduck

I think you would need to use a combination of the PMT function and Goal seek.

If you enter your data as follows:

A1: Annual interest rate
B1: Number of years of the loan
C1: Loan amount- I know you don't know this yet but just pick a figure.

In cell A3, enter the following:

=PMT(A1/12,B1*12,C1,,)

This will calculate the monthly repayment (which will probably be way off your maximum possible amount).

Now you use Goal seek to see how much you could borrow:

Click on the answer you've computed above.
Click Tools | Goal seek
In the dialog box which appears, enter the actual monthly amount you can pay in the 'To value' field.
Click on the 'By changing cell' field and then click on the loan amount you guessed (cell C1).

Excel will compute the loan amount.

This sure is taking the long way around a simple problem.

Please see my solution.