Websoft Pk - We share what we learn

How to manage Loans with Different Durations in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
This example teaches you how to compare loans with different durations.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
1. First, we calculate the monthly payment on a loan with an annual interest rate of 6%, a 20-year duration and a present value (amount borrowed) of $150,000.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Monthly Payment" height="130" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/monthly-payment.png" title="Monthly Payment" width="604" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods).</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
2. Next, select the range A2:D2 and drag it down two rows.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
3. Change the duration of the other two loans to 25 and 30 years.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Result:</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Loans with Different Durations Example" height="170" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/loans-with-different-durations-example.png" title="Loans with Different Durations Example" width="604" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
The monthly payment over 30 years ($899,33) looks good in contrast to the $966,45 and $1,074.65. Right?</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
4. But now we calculate the Total Paid for each loan.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Loans with Different Durations Result" height="170" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/loans-with-different-durations-result.png" title="Loans with Different Durations Result" width="604" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
The monthly payment over 30 years ($899,33) suddenly does not look so attractive anymore. Conclusion: the longer the duration of the loan, the more interest you pay.</div>

How to manage Loans with Different Durations in Excel

This example teaches you how to compare loans with different durations. 1. First, we calculate the monthly payment on a loan with an annu...

How to use Two-way Lookup in Ms Excel

How to use Two-way Lookup in Ms Excel

This example teaches you how to lookup a value in a two-dimensional range. We use the MATCH and INDEX function. Below you can find the sa...

How to Manage Negative Numbers in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
1. Select cell C1.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
2. Enter the formula =A1-B1</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
3. Click the lower right corner of cell C1 and drag the formula down.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Negative Numbers to Zero Example" height="350" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers-to-zero-example.png" title="Negative Numbers to Zero Example" width="346" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Result:</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Negative Numbers" height="350" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers.png" title="Negative Numbers" width="346" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
4. To change the negative numbers to zero but leave the positive numbers unchanged, instead of =A1-B1, use =MAX(0,A1-B1) and drag the function down.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Result:</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Negative Numbers to Zero Result" height="350" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers-to-zero-result.png" title="Negative Numbers to Zero Result" width="346" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Explanation: the MAX function returns the maximum of two values. If the result of the formula is negative, the function returns 0.</div>

How to Manage Negative Numbers in Excel

A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers...

How to make Compound Interest in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
What's compound interest and what's the formula for compound interest in Excel? This example gives you the answers to these questions.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
1. Assume you put $100 into a bank. How much will your investment be worth after one year at an annual interest rate of 8%? The answer is $108.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Compound Interest Example" src="http://www.excel-easy.com/examples/images/compound-interest/compound-interest-example.png" title="Compound Interest Example" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
2. Now this interest ($8) will also earn interest (compound interest) next year. How much will your investment be worth after two years at an annual interest rate of 8%? The answer is $116.64.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Investment After 2 Years" src="http://www.excel-easy.com/examples/images/compound-interest/investment-after-2-years.png" title="Investment After 2 Years" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
3. How much will your investment be worth after 5 years? Simply drag the formula down to cell A6.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Investment After 5 Years" src="http://www.excel-easy.com/examples/images/compound-interest/investment-after-5-years.png" title="Investment After 5 Years" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
The answer is $146.93.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
4. All we did was multiplying 100 by 1.08, 5 times. So we can also directly calculate the value of the investment after 5 years.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Compound Interest Result" src="http://www.excel-easy.com/examples/images/compound-interest/compound-interest-result.png" title="Compound Interest Result" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
which is the same as:</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Compound Interest Formula" src="http://www.excel-easy.com/examples/images/compound-interest/compound-interest-formula.png" title="Compound Interest Formula" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Note: there is no special function for compound interest in Excel. However, you can easily create a compound interest calculator to compare different rates and different durations.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
5. Assume you put $10,000 into a bank. How much will your investment be worth after 10 years at an annual interest rate of 5% compounded monthly? The answer is $16,470.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Compounded Monthly" src="http://www.excel-easy.com/examples/images/compound-interest/compounded-monthly.png" title="Compounded Monthly" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
6. Assume you put $10,000 into a bank. How much will your investment be worth after 15 years at an annual interest rate of 4% compounded quarterly? The answer is $18,167.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Compounded Quarterly" src="http://www.excel-easy.com/examples/images/compound-interest/compounded-quarterly.png" title="Compounded Quarterly" /></div>

How to make Compound Interest in Excel

What's compound interest and what's the formula for compound interest in Excel? This example gives you the answers to these questio...

Investment or Annuity formula for exce

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
This example teaches you how to calculate the future value of an investment or the present value of an annuity.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)?</div>
<h3 style="background-color: white; color: #3e3530; font-family: Arial, Helvetica, sans-serif; font-size: 1.6em; font-weight: normal; margin: 0px; padding: 0px; text-align: justify;">
<a href="" id="investment" name="investment" style="color: #339ccb;"></a>Investment</h3>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Assume that at the end of every year, you deposit $100 into a savings account. At an annual interest rate of 8%, how much will your investment be worth after 10 years?</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
1. Insert the FV (Future Value) function.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.excel-easy.com/examples/images/investment-annuity/insert-fv-function.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.excel-easy.com/examples/images/investment-annuity/insert-fv-function.png" /></a></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<br /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
2. Enter the arguments.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Future Value of the Investment" src="http://www.excel-easy.com/examples/images/investment-annuity/future-value-investment.png" title="Future Value of the Investment" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
In 10 years time, you pay 10 * $100 = $1000 (negative), and you'll receive $1,448.66 (positive) after 10 years. The higher the interest, the faster your money grows.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Note: the last two arguments are optional. If omitted, Pv = 0 (no present value). If Type is omitted, it is assumed that payments are due at the end of the period.</div>
<h3 style="background-color: white; color: #3e3530; font-family: Arial, Helvetica, sans-serif; font-size: 1.6em; font-weight: normal; margin: 0px; padding: 0px; text-align: justify;">
<a href="" id="annuity" name="annuity" style="color: #339ccb;"></a>Annuity</h3>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Assume you want to purchase an annuity that will pay $600 a month, for the next 20 years. At an annual interest rate of 6%, how much does the annuity cost?</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
1. Insert the PV (Present Value) function.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Insert PV function" src="http://www.excel-easy.com/examples/images/investment-annuity/insert-pv-function.png" title="Insert PV function" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
2. Enter the arguments.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
<img alt="Present Value of the Annuity" src="http://www.excel-easy.com/examples/images/investment-annuity/present-value-annuity.png" title="Present Value of the Annuity" /></div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
You need a one-time payment of $83,748.46 (negative) to pay this annuity. You'll receive 240 * $600 (positive) = $144,000 in the future. This is another example that money grows over time.</div>
<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
Note: we receive monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper. The last two arguments are optional. If omitted, Fv = 0 (no future value). If Type is omitted, it is assumed that payments are due at the end of the period. This annuity does not take into account life expectancy, inflation etc.</div>

Investment or Annuity formula for exce

This example teaches you how to calculate the future value of an investment or the present value of an annuity. Tip: when working with fi...

Loans with Different Durations formula for Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;">
This example teaches you how to compare loans with different durations.<br />
1. First, we calculate the monthly payment on a loan with an annual interest rate of 6%, a 20-year duration and a present value (amount borrowed) of $150,000.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.excel-easy.com/examples/images/loans-with-different-durations/monthly-payment.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/monthly-payment.png" /></a></div>
<br />
Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods).<br />
2. Next, select the range A2:D2 and drag it down two rows.<br />
3. Change the duration of the other two loans to 25 and 30 years.<br />
Result:<br />
<img alt="Loans with Different Durations Example" height="170" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/loans-with-different-durations-example.png" title="Loans with Different Durations Example" width="604" /><br />
The monthly payment over 30 years ($899,33) looks good in contrast to the $966,45 and $1,074.65. Right?<br />
4. But now we calculate the Total Paid for each loan.<br />
<img alt="Loans with Different Durations Result" height="170" src="http://www.excel-easy.com/examples/images/loans-with-different-durations/loans-with-different-durations-result.png" title="Loans with Different Durations Result" width="604" /><br />
The monthly payment over 30 years ($899,33) suddenly does not look so attractive anymore. Conclusion: the longer the duration of the loan, the more interest you pay.</div>

Loans with Different Durations formula for Excel

This example teaches you how to compare loans with different durations. 1. First, we calculate the monthly payment on a loan with an annua...

Lower/Upper Case values in Excel

Lower/Upper Case values in Excel

This example teaches you how to convert a text string to lower, upper or proper case in Excel. 1. Use the LOWER function to convert all l...