APY Calculation in Excel

104 4

    Calculating APY From Interest Rates

    • To convert an interest rate to APY, you will need to have the interest rate (APR) and the number of times the interest compounds each year. The formula will look like this, and will produce a percentage:

      =(1+B1/B2)^B2-1

      Cell B1 has the interest rate, and cell B2 has the number of times per year the interest compounds. If it's a credit card, the interest compounds every day, so B2 would have a value of "365." If it's a mortgage, or a typical bank personal line of credit, the compounding period will be monthly, and B2 would have "12."

    Deriving Interest Rates from APY

    • To convert APY to a basic interest rate, you need the given Annual Percentage Yield (APY) and the number of times per year the interest compounds. The formula will look like this, and will produce a percentage:

      =((1+C1)^(1/C2)-1)*C2

      Cell C1 has the APY rate, and cell C2 has the number of times per year the interest compounds. For most investments, interest compounds monthly or quarterly; some certificates of deposit compound once over the entire term of the deposit.

    Reasons For Conversion

    • When banks advertise savings accounts, they refer to APY, because the number will be higher than the base interest rate. Credit card offers always refer to the Annual Percentage Rate, or APR, because it's a lower percentage. Being able to convert APY to interest rates and back allows you to make informed comparisons of financial services products.

    Example: Payday Loan APY and Interest Rates

    • Not all lenders provide their APR and APY directly. Using the formulas above, reverse-engineer the lending rate and APY of a typical payday loan. In this example, the loan is $100, and the payment term is two weeks, in which $115 is payable. This gives you an annual percentage rate of 26 (the number of periods in the year) times 15 percent, or an annual interest rate of 390 percent. Putting 390 percent into the formulas above, along with 26 interest compounding periods, you get an APY of 3685.68 percent.

Subscribe to our newsletter
Sign up here to get the latest news, updates and special offers delivered directly to your inbox.
You can unsubscribe at any time

Leave A Reply

Your email address will not be published.