small excel work it will take one hour
This worksheet will compute the monthly value of an amortized loan for 36 months. This will be a worksheet in which the loan value or principal, interest rate, and monthly payment can be changed and the worksheet will automatically update. Within the worksheet there will be some absolute cell referencing that is needed and some relative cell referencing that is needed. You will need to determine which is appropriate.
Title the worksheet "Amortization of Car Loan" in cell A1. Leave a blank row under the title.
Enter Today's Date in cell G1 so that it updates each time the file is updated in the spreadsheet.
In row five of this worksheet create cells labeled "Amount of Loan," "Interest Rate," and "Monthly Payment," with two columns between each label. Invent reasonable values to put in the three cells below each title as a starting place. These values are temporary values to help you see if your worksheet is working as you create it. For example, you may want to enter $5000 as the loan amount, .065 as the interest rate, and $150 as the monthly payment.
Leave two empty rows below the cells created in the prior step, then label four columns as "Month," "Current Principal," "Interest Due," and "New Principal."
In cells under the "Month" title place the numbers from 1 to 36. Put the number 1 in the first cell and then use cell referencing and cell copying to create the rest of the column by using a formula that adds one to the cell above.
In the first cell under "Current Principal" title reference the value in the "Amount of Loan" cell.
In the first cell under the "Interest Due" column use a formula to calculate the interest that is due on the first value in the "Current Principal" column. You will need to multiply the "Current Principal" by the "Interest Rate," divided by 12. (For the rate, reference the rate value entered in step 2. The rate is a yearly rate and the time is 1/12 of a year.) Use cell referencing in this formula. You will need to use an absolute reference to refer to the "Interest Rate" value. (The formula is I = p*r*t.)
In the "New Principal" column use a formula to compute the new amount due by taking the "Current Principal" and adding the "Interest Due" and subtracting the "Monthly Payment." Again, you must use absolute cell referencing for this.
Make the next "Current Principal" equal to the "New Principal" from the month before.
Complete the second row of the data by copying the rest of the formulas from the cells in the row above. (Check that your references are correct.)
Complete the worksheet by copying all three of the formulas down for the remaining 36 months.
At this point you should be able to determine amounts due after 36 months by changing only the values of the "Amount of Loan," "Interest Rate," and "Monthly Payment." Test your worksheet by entering $15000 into the "Amount of Loan," 0.08 into the "Interest Rate," and $470 as the "Monthly Payment." If you have completed everything correctly the amount due after 36 months is $1.84! The interest due for the first month should be $100.
Place the letters "a," "b," "c," and "d" in a column to the right of the amortization work. Use your worksheet to determine the following and put the answer next to the letter:
Explain this.
After 36 months, how much will be owed on a loan of $15,000 at 6% if the monthly payment is $220?
Find the monthly payment to the nearest dollar (use trial and error and change the payment until you get it) that will pay off a $123 ,000 loan in 36 months if the interest rate is 7%.
Find the monthly payment to the nearest dollar (use trial and error) that will pay off a $7,000 loan in 36 months if the interest rate is 4%.
After 36 months, what happens to a $25,000 loan at 8% if the monthly payment is $135?
Ready for the assignment. I'm doing this kind of work since 2010. I believe that this work best suits me and I meet your expectations with quality and on-time delivery.
Excel, VBA, Macro; Word, Data Entry; 100%TAT, 100% accuracy/ 7 years of experience.
Hello,
I have done several projects like this but new in freelancer. I have confident that I will do your work very well. I hope you would like my work and you will give me a good feedback which is very important for my future career in Freelancer.
Thank you
Md Kamruzzaman
having worked in credit and operations with a total work experience of 14yrs+. I have worked in Credit, Credit Operations & Bank-end Operations for Banks, Insurance, BPO & Real estate Industry. Have an expertise knowledge in arranging the data in spread sheet (including the pictorial representations).
Hi Sir/Ma'am,
I have an experience of more than one year on excel. I can complete your work with in eight days. I guarantee that the quality of work will be upto the mark. You won't bring up any complaints after submitting the project.
Regards,
Ashpreet
I have recently completed my Masters in Information Technology & Management from the University of Texas at Dallas. I have a varied skillset and am extremely proficient in working with Advanced Excel(Macros, Pivot Tables, Vlookup), Excel Add-ins namely Power Query, Power Pivot as well as comfortable performing statistical analysis using Regression, Hypothesis Testing and ANOVA on Excel. I have previously created amortization charts and thereby have a holistic overview of the same.
I am extremely confident of submitting the project ASAP.