Методика расчета кредита и решения транспортной задачи в Microsoft Excel
Методика построения графика дифференцированного платежа. Расчет основного долга в первом и во втором месяце в соответствии с формулой аннуитетного кредита. Характеристика допустимого плана грузовых перевозок, найденного методом северо-западного угла.
| Рубрика | Экономико-математическое моделирование |
| Вид | курсовая работа |
| Язык | русский |
| Дата добавления | 18.05.2015 |
| Размер файла | 133,1 K |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru
Размещено на http://www.allbest.ru
1. Рассчитать погашение кредита тремя способами
Таблица 1. Условия кредита
|
B |
C |
||
|
2 |
Начальная сумма кредита (руб.) |
270000 |
|
|
3 |
Годовая ставка кредита в % |
22 |
|
|
4 |
срок кредита в месяцах |
15 |
Решение.
Способ 1. Дифференцированный платеж с равными долями погашения основного долга. Погашения основного долга - не меняется от месяца к месяцу.
Таблица 2.
|
6 |
A |
B |
C |
D |
E |
F |
|
|
Месяц |
Сумма на начало |
% по кредиту за месяц |
погашение основного долга |
Всего платежей |
Сумма на конец |
||
|
7 |
1 |
270000,00 |
4950 |
18000,00 |
22950,00 |
252000,00 |
|
|
8 |
2 |
252000,00 |
4620 |
18000,00 |
22620,00 |
234000,00 |
|
|
9 |
3 |
234000,00 |
4290 |
18000,00 |
22290,00 |
216000,00 |
|
|
10 |
4 |
216000,00 |
3960 |
18000,00 |
21960,00 |
198000,00 |
|
|
11 |
5 |
198000,00 |
3630 |
18000,00 |
21630,00 |
180000,00 |
|
|
12 |
6 |
180000,00 |
3300 |
18000,00 |
21300,00 |
162000,00 |
|
|
13 |
7 |
162000,00 |
2970 |
18000,00 |
20970,00 |
144000,00 |
|
|
14 |
8 |
144000,00 |
2640 |
18000,00 |
20640,00 |
126000,00 |
|
|
15 |
9 |
126000,00 |
2310 |
18000,00 |
20310,00 |
108000,00 |
|
|
16 |
10 |
108000,00 |
1980 |
18000,00 |
19980,00 |
90000,00 |
|
|
17 |
11 |
90000,00 |
1650 |
18000,00 |
19650,00 |
72000,00 |
|
|
18 |
12 |
72000,00 |
1320 |
18000,00 |
19320,00 |
54000,00 |
|
|
19 |
13 |
54000,00 |
990 |
18000,00 |
18990,00 |
36000,00 |
|
|
20 |
14 |
36000,00 |
660 |
18000,00 |
18660,00 |
18000,00 |
|
|
21 |
15 |
18000,00 |
330 |
18000,00 |
18330,00 |
0,00 |
Заполним таблицу 2 в Excel (ячейки А6:F21) по формулам. Для этого последовательно программируем формулы 7 и 8 строки таблицы Excel. Так «Сумма на начало» в ячейке В7 равна сумме в ячейке С2. Кроме того делаем ее постоянной ссылкой (кнопка F4). Поэтому в ячейке В7 записано: =$C$2.
«% по кредиту за месяц» равны: = «сумма на начало» *«Годовая ставка кредита»/12/100. При этом «сумма на начало» делаем переменной ссылкой т.к. она будет меняться от месяца к месяцу, а «Годовая ставка кредита» делаем постоянной ссылкой. Кроме того, т.к. мы считаем проценты за месяц, то делим на 12 и на 100. Поэтому в ячейке С7 записано =B7*$C$3/12/100.
Погашения основного долга - не меняется от месяца к месяцу. Поэтому в ячейке D7 записано = «Начальная сумма кредита»/ «срок кредита в месяцах»
(делаем их постоянной ссылкой). А в ячейке D8 записано = D7. Тем самым мы задаем постоянство погашения основного долга. «Всего платежей» равны: =«% по кредиту за месяц» + «Погашения основного долга». Поэтому в ячейке Е7 записано: =C7+D7. «Сумма на конец» = «Сумма на начало» - «Погашения основного долга». Поэтому в ячейке F7 записано: =B7-D7.
Далее программируем восьмую строку таблицы.
«Сумма на начало» второго месяца равна «Сумма на конец» первого месяца. Отсюда следует, что в ячейке B8 записано: = F7. Далее копируем формулы из строки 7 в строку 8 в столбцах C,D,E,F.
После этого копируем формулы из строки 8 во все строки ниже в столбцах A,B,C,D,E,F.
Рис. 1. График дифференцированного платежа
На графике видно, что выплаты по основному долгу остаются неизменными в течение всего срока кредита, а сумма процентов по кредиту уменьшается. Такой график платежей наиболее удобен для заёмщика, но не для банка.
Способ 2. Второй кредит - аннуитетный - с постоянными суммарными выплатами = % по кредиту за месяц + погашения основного долга = константа.
Формулы аннуитетного кредита:
Пусть S - сумма кредита; а - % годовая ставка; n - срок кредита.
Погашения основного долга 1 месяца = S*а/12/100/((1+а/12/100)n - 1)
Погашения основного долга 2 месяца = погашения основного долга 1 месяца * (1+а/12/100) и т.д.
Погашения основного долга i+1-го месяца=погашения основного долга «i го» месяца *(1+а/12/100)
В результате столбик «Всего платежей» - константа.
Рассчитать кредит при тех же начальных условиях, что и в способе 1.
Таблица 3
|
6 |
A |
B |
C |
D |
E |
F |
|
|
Месяц |
Сумма на начало |
% по кредиту за месяц |
погашение основного долга |
Всего платежей |
Сумма на конец |
||
|
7 |
1 |
270000,00 |
4950,00 |
15801,77 |
20751,77 |
254198,23 |
|
|
8 |
2 |
254198,23 |
4660,30 |
16091,47 |
20751,77 |
238106,76 |
|
|
9 |
3 |
238106,76 |
4365,29 |
16386,48 |
20751,77 |
221720,28 |
|
|
10 |
4 |
221720,28 |
4064,87 |
16686,90 |
20751,77 |
205033,38 |
|
|
11 |
5 |
205033,38 |
3758,95 |
16992,83 |
20751,77 |
188040,55 |
|
|
12 |
6 |
188040,55 |
3447,41 |
17304,36 |
20751,77 |
170736,19 |
|
|
13 |
7 |
170736,19 |
3130,16 |
17621,61 |
20751,77 |
153114,58 |
|
|
14 |
8 |
153114,58 |
2807,10 |
17944,67 |
20751,77 |
135169,91 |
|
|
15 |
9 |
135169,91 |
2478,11 |
18273,66 |
20751,77 |
116896,25 |
|
|
16 |
10 |
116896,25 |
2143,10 |
18608,67 |
20751,77 |
98287,58 |
|
|
17 |
11 |
98287,58 |
1801,94 |
18949,83 |
20751,77 |
79337,75 |
|
|
18 |
12 |
79337,75 |
1454,53 |
19297,25 |
20751,77 |
60040,50 |
|
|
19 |
13 |
60040,50 |
1100,74 |
19651,03 |
20751,77 |
40389,47 |
|
|
20 |
14 |
40389,47 |
740,47 |
20011,30 |
20751,77 |
20378,17 |
|
|
21 |
15 |
20378,17 |
373,60 |
20378,17 |
20751,77 |
0,00 |
Разместим расчет в ячейках A6:F21. Для этого полностью скопируем всю таблицу расчетов из первого способа.
Поменяем формулу для расчета основного долга в первом и во втором месяце в соответствии с формулой аннуитетного кредита:
Погашения основного долга 1 месяца = S*а/100/12/((1+а/100/12)n - 1)
Где: S - сумма кредита; а - % годовая ставка; n - срок кредита.
Погашения основного долга 2 месяца = погашения основного долга 1 месяца * (1+а/100/12)
Далее копируем формулы второй строки ниже. Для этого выделяем всю вторую строку (массив А7:F7) и тянем вниз за маркер заполнения (точка в правом нижнем углу выделенного массива).
Рис. 2. График аннуитетного платежа
На графике видно, что выплаты по основному долгу к концу срока кредита увеличиваются, а сумма процентов по кредиту уменьшается. Такой график платежей удобен для банка.
Способ 3.
Также как и во втором способе, расчет этой таблицы отличается от предыдущего только начислением основного долга.
Разместим расчет в ячейках A6:F21. Для этого полностью скопируем всю таблицу расчетов из первого способа.
Поменяем формулу для расчета основного долга в первом и во втором месяце. =-ОСПЛТ($C$3/100/12;A51;$C$4;$C$2)
Далее копируем формулы второй строки ниже.
Таблица 4
|
6 |
A |
B |
C |
D |
E |
F |
|
|
Месяц |
Сумма на начало |
% по кредиту за месяц |
погашение основного долга |
Всего платежей |
Сумма на конец |
||
|
7 |
1 |
270000,00 |
4950,00 |
15801,77 |
20751,77 |
254198,23 |
|
|
8 |
2 |
254198,23 |
4660,30 |
16091,47 |
20751,77 |
238106,76 |
|
|
9 |
3 |
238106,76 |
4365,29 |
16386,48 |
20751,77 |
221720,28 |
|
|
10 |
4 |
221720,28 |
4064,87 |
16686,90 |
20751,77 |
205033,38 |
|
|
11 |
5 |
205033,38 |
3758,95 |
16992,83 |
20751,77 |
188040,55 |
|
|
12 |
6 |
188040,55 |
3447,41 |
17304,36 |
20751,77 |
170736,19 |
|
|
13 |
7 |
170736,19 |
3130,16 |
17621,61 |
20751,77 |
153114,58 |
|
|
14 |
8 |
153114,58 |
2807,10 |
17944,67 |
20751,77 |
135169,91 |
|
|
15 |
9 |
135169,91 |
2478,11 |
18273,66 |
20751,77 |
116896,25 |
|
|
16 |
10 |
116896,25 |
2143,10 |
18608,67 |
20751,77 |
98287,58 |
|
|
17 |
11 |
98287,58 |
1801,94 |
18949,83 |
20751,77 |
79337,75 |
|
|
18 |
12 |
79337,75 |
1454,53 |
19297,25 |
20751,77 |
60040,50 |
|
|
19 |
13 |
60040,50 |
1100,74 |
19651,03 |
20751,77 |
40389,47 |
|
|
20 |
14 |
40389,47 |
740,47 |
20011,30 |
20751,77 |
20378,17 |
|
|
21 |
15 |
20378,17 |
373,60 |
20378,17 |
20751,77 |
0,00 |
Как видно из таблицы, ее значения полностью совпадаю с расчетами предыдущим способом.
2. Транспортная задача
Таблица 5. Исходные данные
|
Расстояние до потребителей продукции в км |
Произведено тонн |
||||||
|
Производители |
Потр 1 |
Потр 2 |
Потр 3 |
Потр 4 |
Потр 5 |
||
|
Производитель 1 |
10 |
15 |
25 |
30 |
18 |
1000 |
|
|
Производитель 2 |
5 |
3 |
2 |
2 |
1 |
20000 |
|
|
Производитель 3 |
25 |
26 |
5 |
10 |
5 |
30000 |
|
|
Производитель 4 |
18 |
2 |
5 |
15 |
15 |
5000 |
|
|
Производитель 5 |
5 |
5 |
10 |
10 |
5 |
6000 |
|
|
Производитель 6 |
0,5 |
0,4 |
0,3 |
0,3 |
0,2 |
7000 |
|
|
Потребность потребителей продукции в тоннах |
10000 |
20000 |
15000 |
15000 |
9000 |
9000 |
Первоначальный допустимый план перевозок, найденный методом северо-западного угла, отражен в табл. 6
Таблица 6. Допустимый план перевозок, найденный методом северо-западного угла
|
Объемы перевозок в тоннах |
Вывоз |
||||||
|
Производители |
Потр 1 |
Потр 2 |
Потр 3 |
Потр 4 |
Потр 5 |
||
|
Производитель 1 |
1000 |
1000 |
|||||
|
Производитель 2 |
9000 |
11000 |
20000 |
||||
|
Производитель 3 |
9000 |
15000 |
6000 |
30000 |
|||
|
Производитель 4 |
5000 |
5000 |
|||||
|
Производитель 5 |
4000 |
2000 |
6000 |
||||
|
Производитель 6 |
7000 |
7000 |
|||||
|
Ввоз |
10000 |
20000 |
15000 |
15000 |
9000 |
3067400 |
Таблица 7. Оптимальный план перевозок
|
Объемы перевозок в тоннах |
Вывоз |
||||||
|
Производители |
Потр 1 |
Потр 2 |
Потр 3 |
Потр 4 |
Потр 5 |
||
|
Производитель 1 |
1000 |
0 |
0 |
0 |
0 |
1000 |
|
|
Производитель 2 |
0 |
11000 |
0 |
9000 |
0 |
20000 |
|
|
Производитель 3 |
0 |
0 |
15000 |
6000 |
9000 |
30000 |
|
|
Производитель 4 |
0 |
5000 |
0 |
0 |
0 |
5000 |
|
|
Производитель 5 |
6000 |
0 |
0 |
0 |
0 |
6000 |
|
|
Производитель 6 |
3000 |
4000 |
0 |
0 |
0 |
7000 |
|
|
Ввоз |
10000 |
20000 |
15000 |
15000 |
9000 |
2768100 |
Рис. 3
аннуитетный платеж кредит дифференцированный
Исходя из данных, представленных в таблице № 7 можно сделать следующий вывод: минимальные суммарные транспортные издержки составляют 2768100 тонно-километров, что меньше первоначального допустимого плана в табл. 6, т.е. план стал лучше.
Размещено на Allbest.ru
...Подобные документы
Особенности построения опорных планов транспортной модели методом северо-западного угла, методом минимальной стоимости, методом Фогеля. Оптимизация транспортной модели открытого и закрытого типа с помощью метода потенциала на основе опорного плана.
курсовая работа [68,6 K], добавлен 25.04.2014Определение первичного опорного плана разными способами: методом северо-западного угла, методом минимальной стоимости, методом Фогеля. Перепланировка поставок с помощью метода потенциалов для каждого плана. Анализ эффективности их использования.
контрольная работа [67,2 K], добавлен 06.11.2012Содержание методов аппроксимации Фогеля, потенциала, наименьшей стоимости и северо-западного угла как путей составления опорного плана транспортной задачи на распределение ресурсов с минимальными затратами. Ее решение при помощи электронных таблиц.
курсовая работа [525,7 K], добавлен 23.11.2010Главные элементы сетевой модели. Задача линейного программирования. Решение симплекс-методом. Составление отчетов по результатам, по пределам, по устойчивости. Составление первоначального плана решения транспортной задачи по методу северо-западного угла.
контрольная работа [747,3 K], добавлен 18.05.2015Пример решения графическим методом задачи линейного программирования с двумя неизвестными. Решение транспортной задачи методами северо-западного угла и минимальной стоимости. Стохастическая модель управления запасами, ее значение для предприятий.
контрольная работа [606,2 K], добавлен 04.08.2013Решение графическим методом задачи линейного программирования с двумя неизвестными. Решение транспортной задачи методом северо-западного угла и методом минимальной стоимости. Системы массового обслуживания. Стохастическая модель управления запасами.
контрольная работа [458,1 K], добавлен 16.03.2012Составление плана перевозок зерна с учетом данных о потребности в нем и его запасах. Минимизация затрат на реализацию плана перевозок. Методы "северо-западного угла" и "минимального элемента". Новый улучшенный опорный план по методу потенциалов.
задача [48,5 K], добавлен 24.05.2009Основные подходы и способы решения транспортной задачи, ее постановка и методы нахождения первоначального опорного решения. Математическая модель транспортной задачи и алгоритм ее решения методом потенциалов. Составление опорного плана перевозок.
курсовая работа [251,0 K], добавлен 03.07.2012Математическая постановка задачи и выбор алгоритма решения транспортной задачи. Проверка задачи на сбалансированность, её опорное решение и метод северо-западного угла. Транспортная задача по критерию времени, поиск и улучшение решения разгрузки.
курсовая работа [64,7 K], добавлен 14.10.2011Математическая постановка и алгоритм решения транспортной задачи. Сбалансированность и опорное решение задачи. Методы потенциалов и северо-западного угла. Блок-схема. Формы входной и выходной информации. Инструкция для пользователя и программиста.
курсовая работа [113,8 K], добавлен 10.11.2008Понятие классической транспортной задачи, классификация задач по критерию стоимости и времени. Методы решения задач: симплекс, северо-западного угла (диагональный), наименьшего элемента, потенциалов решения, теория графов. Определение и применение графов.
курсовая работа [912,1 K], добавлен 22.06.2015Применение линейного программирования для решения транспортной задачи. Свойство системы ограничений, опорное решение задачи. Методы построения начального опорного решения. Распределительный метод, алгоритм решения транспортной задачи методом потенциалов.
реферат [4,1 M], добавлен 09.03.2011Типы транспортных задач и методы их решения. Поиск оптимального плана перевозок методом потенциалов. Решение задачи с использованием средств MS Excel. Распределительный метод поиска оптимального плана перевозок. Математическая модель, описание программы.
курсовая работа [808,7 K], добавлен 27.01.2011Решение задачи линейного программирования графическим и симплекс-методом. Способы решения транспортных задач: методы северо-западного угла, наименьшей стоимости и потенциалов. Динамическое программирование. Анализ структуры графа, матрицы смежности.
курсовая работа [361,8 K], добавлен 11.05.2011Общее понятие о прогнозировании, методы. Абсолютные, сравнительные и качественные показатели оценки качества прогноза. Метод наименьших квадратов. Модели линейного роста. Новшества программы Excel 5.0. Пример решения задачи по прогнозу объема кредита.
курсовая работа [1,1 M], добавлен 07.08.2013Определение транспортных задач закрытого и открытого типов. Построение опорных планов методом северо-западного угла, минимальной стоимости и методом Фогеля. Анализ оптимального плана по перевозке груза. Достижение минимума затрат и времени на перевозку.
курсовая работа [6,2 M], добавлен 05.11.2014Алгоритм решения оптимизационной задачи линейного программирования (ЗЛП) – планирования производства симплекс методом и при помощи средства "Поиск решения" в Microsoft Excel. Описание работы, графический интерфейс и схема программы для решения ЗЛП.
дипломная работа [2,3 M], добавлен 19.09.2010Математическая формулировка экономико-математической задачи. Вербальная постановка и разработка задачи о составлении графика персонала. Решение задачи о составлении графика персонала с помощью программы Microsoft Excel. Выработка управленческого решения.
курсовая работа [1,2 M], добавлен 12.01.2018Особенности формирования и способы решения оптимизационной задачи. Сущность экономико-математической модели транспортной задачи. Характеристика и методика расчета балансовых и игровых экономико-математических моделей. Свойства и признаки сетевых моделей.
практическая работа [322,7 K], добавлен 21.01.2010Методика и этапы построения экономических моделей с помощью программы Microsoft Excel. Определение оптимальной структуры производства консервного завода на основании имеющихся статистических данных. Нахождение условного экстремума функции в Excel.
контрольная работа [1,4 M], добавлен 01.06.2009
