Solve for return needed for one more period to equal a required APY

Back on

After you entered the periods (fractions of a year), the APY and APR are returned. In this sample, the rates were quarterly, so the number of years was also an output (2.75 years).

Assume that after you have made these calculations for your boss, he/she asks what final return this next quarter would be needed to earn a 9.00% APY for the full three years. That is what this new spreadsheet is for.

Enter the required APY (in this case 9.0%) and the answer is returned (3.420%). The extended answer is more accurate, so copy/past the answer into the cell after the last quarterly number, and the calculator now equals a 9.00% APY.

The math used to calculate the answer looks like this:

Download http://pistulka.com/Excel_Shared/Chain2.xlsm

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

Your email address will not be published. Required fields are marked *