Loan Payment calculation

[amit.shukla]

Hi Team,

I was going through your videos and had a small query, that needed some answers. Hope you could help.

Query :

1) if employees are paying loan amount with different yearly interest rates with every new request, whereas the fixed amount is 35,000, and the amount to be paid to the employee is 70% of the fixed amount with the interest rate. The loan amount has to be paid monthly, so how do we apply a formula, and which formula to get the result.

Let me share an example so that you can help us with our query.


Hire Amount : 70,000
Loan Amount : 58,000
Interest Rate : 3.88%
Fixed Amount : 35,000

Loan: 84 months

How to create a formula to ensure that we get the monthy amount for them?

2) Example for my second query :

Request Submitted Date : 21-Sep-11
Installment Start Date : 30-Sep-11 / 31-Sep-08
Maximum time for which payment can be processed: 60


In the second query, I am providing you two dates, so that we can get a better calrity on my query.

2(a) : If employee's loan payment start date is 31-Sep-08, and he has submitted the request on 21-Sep-11, the system should take the latest date(no matter when the payment start date is)

2(b) : If the installment start date is after the request submitted date, the system should pick the future date automatically.

2(c): System should be able to calculate 60 months from the day ( Installment start date or Submission Date), provided whichever is applicable. Incase, the installment end date is less than 60 months time, system should be able to pick it up automatically as well.

Do we have two formulas for them, one for amount and other for date calculation?

If yes, Kindly assist me with the same.

Regards,
Amit Shukla

 

[MrExcel]

Loan Payments are calculated with the PMT function.

If the principal is $35000, the rate is 3.88% and the term is 84 months, the monthly payment is calculated with =PMT(0.0388/12,84,-35000).

Note that the rate must be divided by 12, because these are monthly payments.
Note that making the principal negative will cause the payment amount to be positive.

For your date issue, to find the later of two dates stored in A1 and C1), use =MAX(A1,C1). You might need to format the resulting cell as a date.

If the date for the first monthly payment is in A5, then use this formula in A6: =DATE(YEAR(A5),MONTH(A5)+1,DAY(A5)). Copy the formula down for the right correct number of rows.

I realize that you wanted the formula to automatically copy down the correct number of rows based on the term. This could be done with a macro or by modifying the formulas to display blanks if this row is beyond the number of months in the term.

For example, say that the loan is for 48 months and that you store 48 in cell E1.
The payment for the first month appears in row 5. This would mean that any rows at (5+48) should not display a value. The formula in A6 could be modified to say =IF(ROW(A6)>=(5+E1),"",DATE(YEAR(A5),MONTH(A5)+1,DAY(A5)))

 

[amit.shukla]

Hi Bill,

Thank You for your response. I am really happy by the response received from your end, however, I still have a query answer to which might help me get the correct answer.

The payment has to be 70% of the fixed amount, which is 35,000 as per my query.

As per the query, it should be "35,000* Interest Rate*70%/12", because it has to be a monthly payment, is there any way, we can get a formula to the formula, I just posted.

Also, thank you for the date formula, but, for a future date calculation, what I was looking for was as follows :

Example 1 :

Requested Date : 21-Sep-11
Payment Start Date : 31-Oct-08
Number of payments : 48

The person has submitted the request on 21-Sep-11, so we will start paying him from the 21-Sep-11, and as per the number of payments, they end in 2012, so the system should be able to calculate the end date accordingly.

We can pay at the max of 60 month payment for any employee(provided if his last payment date completes 60 months from date of request submitted), but, if in case, the employee's payment has to be ended before 60 months, as he only has 10 or 5 or 6 months left for his payment to be over, the system should be able to calculate that as well.

I tried the formula provided by you at the end of the reply, but I couldn't really get it, could you provide an input on it.

Also, can I have your email address so that I can send you the file, that contains details of what I want.

If you have any email account, I could share the excel file with you.

Regards,
Amit Shukla

 

[MrExcel]

48 payments would end in 2015. Or are these not monthly payments?

Is query 2 not related to the loan in query 1?

 

[MrExcel]

Reading original post, the loan was $58,000, but they only have to pay back 70% of 35,000? That is a nice deal for the employee.

=PMT(Rate,Term,Principal)
Rate is 3.88%/12 or 0.0388/12
Term is 60? or 84?
Principal is -35000*.7

The formula would become
=PMT(0.0388/12,60,-35000*.7)

Bill

 

[amit.shukla]

Hi Bill,

Both Query 1 & 2 are for the same.

Ideally, its only query 1, query 2 was a clarification to the first query.

Yes, its right we only pay 70 % of the amount, i.e., (70%*35,000*Interest rate/12) per month of Employee.

Interest rate that we receive with request form is in the format : 3.88% not 0.0388

Also, we calculate the Installment end date, from the actual installment start date, and not, from the request submission date. We pay the employee until his actual last payment date per month. The payment starts from whichever date is latest. If (requested date>payment start date=requested date), if (requested date<payment start date=payment start date).