gareece Posted June 24, 2004 Share Posted June 24, 2004 FM seems to use a very simple method to calculate payments on a loan. The formula as I understand it is: PMT ( principal ; interestRate ; term ) I need to include a residual value and type (ie. payment at beginning/end of term). In Excel I would use the following formula: PMT(rate,nper,pv,fv,type) Any ideas on how I can use all 5 variables. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 24, 2004 Share Posted June 24, 2004 Hi, You might be interrested by this thread Link to comment Share on other sites More sharing options...
gareece Posted June 25, 2004 Author Share Posted June 25, 2004 Thanks for your reply Ugo but I am struggling to understand how I can resolve my problem. As an example I need to calculate the monthly payments for the following set of variables: Amount financed = $20,000 Annual interest rate = 8.00% Residual value = $5000 Term = 60 months Payments are in advance I appreciate your help. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 25, 2004 Share Posted June 25, 2004 Hey, Maths here, calling Mister Queue for Help ! : n = Term => 60 months L = Amount financed => $ 20,000 m = Monthly Payment => : F= Future Value=> $5,000 r = Annual interest rate =>8 t = rate/100 To verify that the equation is true : L - (m/t)- ((L+F)/(1-(1+t)^n))=0 So m should be : t x (L-((L+F)/(1-(1+t)^n))) that is (Annual interest rate/100)*((Amount financed+Residual value)/(1-(1+(Annual interest rate/100))^Term))) HTH Link to comment Share on other sites More sharing options...
-Queue- Posted June 25, 2004 Share Posted June 25, 2004 m = ( -F -L * ( 1 + (rate/12) ) ^ n ) / (( 1 + (rate/12) * type ) * (( 1 + (rate/12) ) ^ n - 1) / (rate/12) ) m = ( -5000 -20000 * 12.08/12 ^ 60 ) / (( 12.08/12 * 1 ) * ( (12.08/12) ^ 60 - 1 ) / (0.08/12) ) m = -34796.914166032219935927687858688 / 73.966701953543260516254043333034 m = -470.44, i.e. payments are $470.44 per month So, create a calculation equal to ( -FV -PV * ( 1 + (RATE/12) ) ^ NPER ) / (( 1 + (RATE/12) * TYPE ) * (( 1 + (RATE/12) ) ^ NPER - 1) / (RATE/12) ) where RATE is the whole rate over 100, e.g. 0.08 for this example, and TYPE equals 0 for end of period or 1 for beginning of period. Note that 1 + (RATE/12) can be written as (12 + RATE)/12 if you prefer. Ugo, it figures you would make me work for my 1800th post. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 26, 2004 Share Posted June 26, 2004 And I was sure you'd soon come to the rescue Mate ! Link to comment Share on other sites More sharing options...
George TOUBALIS Posted June 26, 2004 Share Posted June 26, 2004 1-0 :-) Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted June 26, 2004 Share Posted June 26, 2004 Hey Georges ! Excellent ! Thanks for kicking out these presumptuous boys. Today noone here got on my nerves, and it was therefore a wonderful sunny day... Forza Grecia ! Link to comment Share on other sites More sharing options...
gareece Posted July 12, 2004 Author Share Posted July 12, 2004 Wow - fantastic! Sorry for taking so long to acknowledge this comprehensive answer. Thanks very much. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7086 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now