Step Click on cell C2 and press the equals button. Step Enter the APY formula, referring back the cells for values of 'i,' and 'N' as needed. By clicking on a certain cell, Excel will enter the value of those cells into your formula. So, click on cell A2 for 'i' and cell B2 for 'N.' The formula should look like this: =(1+(A2/B2))^B2-1. Press enter when you are finished entering the formula.

It will give you the interest rate in decimal form. Step Continue entering values for 'i' and 'N' going down columns A and B if you need to perform more APY calculations. After entering more values, all you have to do to run the calculations is click on the lower right-hand corner of cell C2 and drag the cell down for as many rows as necessary.

Any one out there know how to calulate APR (annual percentage rate) in excell 2003? Please response, thanks. Download excel apy to apr calculator spreadsheet calculator online for free.

Excel will remember the formula used in C2 and perform the same calculation on each row using the new inputs for 'i' and 'N.' Galliano A Joyful Noise Unto The Creator Rare more.

Is there a way with Excel to calculate the APR for an adjustbale rate loan? I'm trying to figure out how to calculate the APR for an ARM loan that has a rate set for three years (initial rate) then the rate adjusts to the index plus the margin (2.75+1.25=4.00%) for the remainiing 27 years. Here's the data I'm using to test: Initial Loan amount: $200,000 Loan fees: $1,500 (includes any prepaid interest) Loan term: 360 months Initial interest rate: 6% Initial term: 36 months Estimated index rate: 4% Margin: 2.75% Index: 1.25% The APR will be lower than the start rate of 6%. What formula in Excel will ehlp me calullate the APR? 4a A B C 1 Principal $200,000 2 Term years 30 3 initial 3 4 subsequent 27 5 Rate 6 initial 6% 7 subsequent 4% 8 Fees 1,500.00 9 Payment 1,008.69 10 Calculated rate 4.5247% 11 Payment calc 1,008.69 0.00 Excel 2003 Worksheet Formulas Cell Formula B10 =RATE( B2*12,-B9,B1-B8,0.044/12)*12 C11 =FV( B7/12,B4*12,-B11,-FV( B6/12,B3*12,-B11,B1)) Welcome to the forum. If you know the fixed payment amount, try the formula 'calculated rate'. If you need to calculate the payment, consider Goal Seek with either an amortization chart or a formula like 'Payment calc'.

I used a guess of a payment of $1,000 and Goal Seek calculated the required payment amount with the formula in the adjacent cell. If you built an amortization chart, you could use Excel's function IRR. Thank you Dave, sorry I should have included the payments. When I change the payment you estimated to the payment for a 30 year amortization as requried for the loan the APR in your formula recalculates to the same APR as a 30 year fixed rate loan. It looks like the rate adjustment was not included in the calculation. To calculate the APR I have to use a replayment schedule that includes the payment for the first rate period 36 months and then the second rate period until the loan pays off in the remaining 27 years.

The loan structure looks like this: Payment for the first 36 months @ 6.00% interest is $1,199.10. The balance of the loan at the end of 36 months would be $192,168.20. Interest paid over the 36 mohts would be $35,335.80.

Then the interest rate adjusts to match the index plus the margin to a rate of 4.00% which results in a payment of $970.85 for the life of the loan. The APR for this loan including loan fees should be 4.397%. The total payments made for the entire loan period will be $357,722.59 and the total interest paid will be $157,722.59. I hope this helps and again sorry I did not provide the payments and extra detail you would need to understand my question in my first post. Thank you for your help. I'm using Excel 2007.

I built an APR formula for fixed rate loans using Excel Rate and the results match all the online calulators I've tested against. Here's how my fixed rate APR Rate formula works: Results for APR 5.148% =RATE(L23*12,-N23,(M23-Q23-O23),H23)*12 L23 = term (years) 30 N23 = Payment $2,270.51 M23 = Loan amount $417,000 Q23 = closing costs $1,077.00 O23= Pre Paid Interest $1,780.94 H23 = Interest rate 5.125% One of the online ARM APR calulators I've test against is: I've also tested against others. All online calulators have the same results. As common as ARM loans are I would think the formula would be easier to find. It has been a real challenge to figure out how to calculate ARM APR in Excel. Thank you for your help. Greg, I have a few comments, then a question.

The first 36 payments are in the range B15 through B50, and the remaining 324 payments are in B51 through B374. At the end of 36 months Note #3 says the balance is $192,168.20 and the interest paid is $35,335.80. Thirty six payments of 1199.10 is $43,167.60, minus the $35,335.80 interest means the principal paid was $7,831.80. Adding the principal paid to the principal balance of $192,186.20 means you started with an initial loan of $200,000. Your 4.508% IRR from Excel starts with an initial loan of $198,500.