Using excel financial functions for determining principal paid

[phineas629]

Hi,

I'm looking for a vba solution to determine when a loan will reach 80% and 50% paid principal.

Currently, I am using excel's native financial functions and looping through until I reach those points.
For every payment I separate the interest and the principal paid. I subtract the principal paid from the loan amount.
I do this until the loan balance reaches the 80% mark and the 50% mark. Is there a more effective manner to do this rather than using a loop? This becomes problematic when I have a large number of loans.

I couldn't figure out how to do it with Excel's native functionality.

I am not looking for a solution that uses the spreadsheet. I want to do it in vba.

Thank you

[quekbc]

Maybe it could be solved arithmetically. What's the repayment method for the loan?

[joeu2004]

I'm looking for a vba solution to determine when a loan will reach 80% and 50% paid principal. [....] I am not looking for a solution that uses the spreadsheet. I want to do it in vba.

Understood. But it might be best to start with Excel.

Suppose the loan amount is in B1, the periodic interest rate is in B2, and the number of payments is in B3. The payment in B4 is:

=PMT(B2,B3,-B1)

Suppose the percentage paid (e.g. 50% or 80%) is in B5. The number of payments is:

=NPER(B2,B4,-B1,B1*(1-B5))

In VBA:

Please Login or Register  to view this content.

Caveat: I am using the VBA functions Pmt and NPer here. Alternatively, use the Excel-like functions WorksheetFunction.Pmt and WorksheetFunction.NPer. There is a subtle difference in results.

[MarvinP]

Hi Phineas and welcome to the forum,

I've been hunting for some examples of VBA financial functions and found the following link. I think it expresses my opinion better than I can.

http://www.proformative.com/question...-pros-and-cons

My opinion is "Why would you want to do VBA for a built in function that already works?"

[phineas629]

I was hoping there would be a way to calculate with excel's native functionality but I don't know the correct financial function to use. If you know then please share.

What I don't want to do is paste a loan schedule on a sheet to determine when principal paid has reached a certain percentage. That can be tedious when looking at thousands of loans.


Thanks

Hi Phineas and welcome to the forum,

I've been hunting for some examples of VBA financial functions and found the following link. I think it expresses my opinion better than I can.

http://www.proformative.com/question...-pros-and-cons

My opinion is "Why would you want to do VBA for a built in function that already works?"

[joeu2004]

I was hoping there would be a way to calculate with excel's native functionality but I don't know the correct financial function to use. If you know then please share.

Did you not see my response #3?!

[kvsrinivasamurthy]

Pl see file with UDF, which gives result in months.
Code

Please Login or Register  to view this content.

[phineas629]

Hi, I missed it since I was typing on my phone. I'll give that function a try.

[phineas629]

How would this UDF be changed to handle changes in interest rate?

Pl see file with UDF, which gives result in months.
Code

Please Login or Register  to view this content.

[phineas629]

Hi Joue,

Thank you for explanation with the function. That helped a lot.

Can this handle changes in interest?

[kvsrinivasamurthy]

YrlyRate Can be entered in a cell and give that cell reference in the function.

[phineas629]

Would you be able to show me how to do it?

I am not familiar with the financial functions. It would be easier if I had a working example to break down.
I think after I see how it's done on the worksheet like you show then I should be able to do it completely in vba.

Thanks for the help thus far guys!

YrlyRate Can be entered in a cell and give that cell reference in the function.

[joeu2004]

Hi Joue, Thank you for explanation with the function. That helped a lot. Can this handle changes in interest?

You're welcome.

If you mean different fixed rates throughout the life of the loan, simply modify B2.

But if you mean variable rates during the life of the loan, no Excel function can handle that. You will need to use a VBA implementation similar to the UDF suggested by kvsrinivasamurthy. However, Loan, YrlyRate, Pmt and ResLoan should be type Double. And it would be prudent use the declaration ByVal YrlyRate, just in case the UDF is called from another VBA procedure, since YrlyRate is changed within the UDF.

For details, first you will need to explain how you want to specify the list of applicable interest rates. For example, you might pass a 2-dimensional range where one column is the interest rate and another column is the first payment number when the interest rate applies.