sviksna
asked on
PPMT function in Excel
Hello, Experts!
I have met PPMT function in excel. I found information ow to use it, but dunnow what this functuion presents from itself.
It would be great to hear from you the formul, how is made this PPMT.
Best regards and hope on you help,
Sandis.
I have met PPMT function in excel. I found information ow to use it, but dunnow what this functuion presents from itself.
It would be great to hear from you the formul, how is made this PPMT.
Best regards and hope on you help,
Sandis.
See under PPMT and PV in Help|Content&Index: Find. Then delete _both_ questions (you posted in duplicate)
ASKER
Could you tell me the diference of the PPMT an VP. I am not very good at that all, so i'd realy like to hear from you that! :)
--
sandis
--
sandis
sviksna,
The following two paragraphs are right out of the Help, Contents and Index description for both items from Excel. I agree with cri that you delete this question and use the help from Excel itself to answer this question.
PPMT: Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
PV: Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Hope this helps.
The following two paragraphs are right out of the Help, Contents and Index description for both items from Excel. I agree with cri that you delete this question and use the help from Excel itself to answer this question.
PPMT: Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
PV: Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Hope this helps.
There's also a fairly nice site here, though it does not contain that particular function:
http://artsci-ccwin.concordia.ca/inte/inte298s/courses/xl/xlfuns.htm
http://artsci-ccwin.concordia.ca/inte/inte298s/courses/xl/xlfuns.htm
ASKER
Well...
I don't need to know what it retunrs and how to use it.. i wana know the formula it presents inside this PPMT!
let's say so.. function myCalculations(rate, value, count) needs 3 (PPMT also is function also got n parameters) parameters and it looks in action like myCalculations(45%, 1/2, 45);
so, you get output, say 34% from value;
but how we get it???????
we get it becouse
myCalculations(rate, value, count)
begin
myCalculations.value_to_re turn = value / count * rate
end
"value / count * rate"
so... THIS IS the FORMULA of myCalculations!!!
I wana know, what operations are made and what formula is hidden in the PPMT function!!!
That is the anwer i'd like to hear!?!?
I don't need to know what it retunrs and how to use it.. i wana know the formula it presents inside this PPMT!
let's say so.. function myCalculations(rate, value, count) needs 3 (PPMT also is function also got n parameters) parameters and it looks in action like myCalculations(45%, 1/2, 45);
so, you get output, say 34% from value;
but how we get it???????
we get it becouse
myCalculations(rate, value, count)
begin
myCalculations.value_to_re
end
"value / count * rate"
so... THIS IS the FORMULA of myCalculations!!!
I wana know, what operations are made and what formula is hidden in the PPMT function!!!
That is the anwer i'd like to hear!?!?
This question is better suited for a Finance forum in Experts-Exchange!
Here is pseudocode to show roughly how ppmt works
Function ppmt(Rate, Per, NPer, PV)
' Rate = Interest rate per period
' Per = Which period you want to calculate principal payment
' Nper = # Periods in the annuity or loan
' PV = Present Value of the annuity or loan
' Calculate the payment per period
ThisPmt = Application.WorksheetFunct ion.Pmt(Ra te, NPer, PV)
' Loop through each year up to Per
For i = i to Per
' Calculate the Interest portion for this period
Int = Rate * PV
' Calculate the Principal portion for this period
Prin = ThisPmt - Int
' Calculate the new PV at the end of Period I
PV = PV - Prin
Next i
PPmt = Prin
End Function
Bill
Here is pseudocode to show roughly how ppmt works
Function ppmt(Rate, Per, NPer, PV)
' Rate = Interest rate per period
' Per = Which period you want to calculate principal payment
' Nper = # Periods in the annuity or loan
' PV = Present Value of the annuity or loan
' Calculate the payment per period
ThisPmt = Application.WorksheetFunct
' Loop through each year up to Per
For i = i to Per
' Calculate the Interest portion for this period
Int = Rate * PV
' Calculate the Principal portion for this period
Prin = ThisPmt - Int
' Calculate the new PV at the end of Period I
PV = PV - Prin
Next i
PPmt = Prin
End Function
Bill
ASKER
Bill,
thanx for VB code but i need it for PHP :)
Maybe you could tell me where is that Finance forum?
thanx for VB code but i need it for PHP :)
Maybe you could tell me where is that Finance forum?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
The payments are calculated by iteration rather than by a direct formula; take a formula for any of the annuities or perpetuities and plug in the other variables until you get a result. If you do this by binary iteration (dividing the remaining data in half), you should get a good result in ten tries. Note that Excel has a default of 10 iteration tries in its options"
Since the payments include interest that is usually variable depending on the specific date, you should be able to calculate it for that specific date, then simply substract the interest portion and you will get the principal portion of the payment.
ex. Future Value of an annuity due (use & for an exponent):
FVA=PMT[((1+r)&n - 1)/r]&(1+r)
Enjoy
The payments are calculated by iteration rather than by a direct formula; take a formula for any of the annuities or perpetuities and plug in the other variables until you get a result. If you do this by binary iteration (dividing the remaining data in half), you should get a good result in ten tries. Note that Excel has a default of 10 iteration tries in its options"
Since the payments include interest that is usually variable depending on the specific date, you should be able to calculate it for that specific date, then simply substract the interest portion and you will get the principal portion of the payment.
ex. Future Value of an annuity due (use & for an exponent):
FVA=PMT[((1+r)&n - 1)/r]&(1+r)
Enjoy
If no objections are made in the next 5 days, I will force accept the contribution by jelen.
costello
Community Support Moderator @ Experts-Exchange
P.S. This post is made as part of the general effort to clean up this topic area, meaning, looping through all the open questions with no activity since 21 days.
costello
Community Support Moderator @ Experts-Exchange
P.S. This post is made as part of the general effort to clean up this topic area, meaning, looping through all the open questions with no activity since 21 days.
Answer accepted by:
ComTech
Community Support Moderator
ComTech
Community Support Moderator