VBA FINANCE EXCEL ASSAIGNMENT
Budget: €30 – €250 EUR
PART 1: We want to build an amortization plan for a loan. This loan will have a varying interest rate.
PART 2: In this second part we still want to build an amortization plan but for a loan with a changing annual payment over a fixed period.
This process is very similar to the one we implemented in the first part. Instead of changing the interest rate we will change the annual payment. Starting with an estimation calculated using the following formula:
?????? ??????? = ??????? ?????? ∗ ???????? ????
This will be used as the annual payment for year 1 and may be adjusted all along the loan’s life based on the data provided in the worksheet “Data”.
Based on this estimated annual payment we will calculate the amortization data and check if the final balance (at the end of the last year) is less than 1. If it is then we succeeded and must just display the result else, we should rebuild the amortization plan after adjusting the Annual Payment to be used for year 1 applying the formula below:
(????? ??????? ∗ (? + ???????? ????)??????? ????????
?????? ??????? = ?????? ??????? + ?????? ???????? ????? ?????? ??????? ?????????????
Our algorithm will use the following arrays (all of them having a size equal to the Loan’s duration in term of years):
PART 2: In this second part we still want to build an amortization plan but for a loan with a changing annual payment over a fixed period.
This process is very similar to the one we implemented in the first part. Instead of changing the interest rate we will change the annual payment. Starting with an estimation calculated using the following formula:
?????? ??????? = ??????? ?????? ∗ ???????? ????
This will be used as the annual payment for year 1 and may be adjusted all along the loan’s life based on the data provided in the worksheet “Data”.
Based on this estimated annual payment we will calculate the amortization data and check if the final balance (at the end of the last year) is less than 1. If it is then we succeeded and must just display the result else, we should rebuild the amortization plan after adjusting the Annual Payment to be used for year 1 applying the formula below:
(????? ??????? ∗ (? + ???????? ????)??????? ????????
?????? ??????? = ?????? ??????? + ?????? ???????? ????? ?????? ??????? ?????????????
Our algorithm will use the following arrays (all of them having a size equal to the Loan’s duration in term of years):